Хранимые процедуры / схема БД в системе управления версиями

Мы храним хранимые процедуры в системе контроля версий.

Создайте сценарий для всего (создание объекта и т. д.) И сохраните эти сценарии в системе управления версиями. Как туда попадают изменения? Это часть стандартной практики работы. Нужно добавить стол? Напишите сценарий CREATE TABLE. Обновить sproc? Отредактируйте сценарий хранимой процедуры.

Я предпочитаю один сценарий для каждого объекта.

создайте «проект базы данных» в Visual Studio, чтобы писать код sQL и управлять им, а также держать проект под контролем версий вместе с остальной частью вашего решения.

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

Для изменений схемы вам может потребоваться регистрировать скрипты, чтобы постепенно вносить изменения, которые вы сделали. Напишите сценарий, примените его, а затем отметьте. Затем вы можете построить процесс для автоматического последовательного применения каждого сценария схемы.

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

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

Мне было бы интересно услышать, могут ли люди делать это автоматически.

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

01.CreateUserTable.sql
02.PopulateUserTable
03.AlterUserTable.sql
04.CreateOrderTable.sql

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

Я настоятельно рекомендую поддерживать схему и хранимые процедуры в системе управления версиями.

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

Схема - менее очевидный ответ в зависимости от того, что вы имеете в виду. Очень полезно поддерживать SQL, который определяет ваши таблицы в системе управления версиями, для дублирования сред (prod / dev / user и т. д.).

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

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

Мы выбираем сценарий для всего, включая все хранимые процедуры и изменения схемы. Никаких wysiwyg-инструментов и причудливых «синхронизирующих» программ не требуется.

Изменять схему легко, все, что вам нужно сделать, это создать и поддерживать единый файл для этой версии, включая все изменения схемы и данных. Это становится вашим сценарием преобразования из версии x в x + 1. Затем вы можете запустить его для производственной резервной копии и интегрировать ее в свою «ежедневную сборку», чтобы убедиться, что она работает без ошибок. Обратите внимание, что важно не изменять и не удалять уже записанный sql для загрузки схемы / данных, так как вы можете в конечном итоге сломать любой sql, написанный позже.

-- change #1234
ALTER TABLE asdf ADD COLUMN MyNewID INT
GO

-- change #5678
ALTER TABLE asdf DROP COLUMN SomeOtherID
GO

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

При использовании Sql Server синтаксис выглядит следующим образом:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_MyProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [usp_MyProc]
GO

CREATE PROCEDURE [usp_MyProc]
(
    @UserID INT
)
AS

SET NOCOUNT ON

-- stored procedure logic.

SET NOCOUNT OFF

GO  

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

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

дополнение: Рик прав, что вы потеряете разрешения для хранимых процедур с помощью DROP / CREATE, поэтому вам может потребоваться написать другой сценарий, который повторно включит определенные разрешения. Этот сценарий разрешения будет запущен последним. Наш опыт обнаружил больше проблем с семантикой ALTER verses DROP / CREATE. YMMV

Пара разных точек зрения из моего опыта. В мире Oracle всем управляли сценарии «создания» DDL. Как упоминал Ахокли, по одному сценарию для каждого объекта. Если объект необходимо изменить, его сценарий DDL изменяется. Есть один сценарий-оболочка, который вызывает все объектные сценарии, чтобы вы могли развернуть текущую сборку БД в любой среде, какой захотите. Это для создания основного ядра.

Очевидно, что в реальном приложении всякий раз, когда вы запускаете новую сборку, которая требует, скажем, нового столбца, вы не собираетесь отбрасывать таблицу и создавать ее новую. Вы собираетесь выполнить сценарий ALTER и добавить столбец. Таким образом, каждый раз, когда должно произойти такое изменение, всегда нужно делать две вещи: 1) написать alter DDL и 2) обновить ядро ​​create DDL, чтобы отразить это изменение. Оба переходят в систему управления версиями, но единственный сценарий изменения является скорее моментальным изменением момента времени, поскольку он будет использоваться только для применения дельты.

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

В мире Microsoft мы использовали аналогичную тактику, но мы использовали продукт Red Gate, чтобы помочь управлять скриптами и дельтами. По-прежнему помещаем скрипты в систему контроля версий. По-прежнему один сценарий для каждого объекта (таблица, sproc, что угодно). Вначале некоторые администраторы баз данных действительно предпочитали использовать графический интерфейс SQL Server для управления объектами, а не сценарии. Но это сильно затрудняло последовательное управление предприятием по мере его роста.

Если DDL находится в системе управления версиями, использовать любой инструмент сборки (обычно ant) ​​для написания сценария развертывания тривиально.

В прошлом опыте я контролировал источник изменений базы данных таким образом, чтобы для каждого выпуска продукта любые изменения базы данных всегда записывались в сценарий и сохранялись в выпуске, над которым мы работаем. В процессе сборки база данных будет автоматически обновлена ​​до текущей версии на основе таблицы в базе данных, в которой хранится текущая версия для каждого «приложения». Написанное нами пользовательское служебное приложение .net затем будет запускаться и определять текущую версию базы данных, а также запускать для нее любые новые сценарии в порядке номеров префиксов сценариев. Затем мы запускали модульные тесты, чтобы убедиться, что все в порядке.

Мы бы сохранили скрипты в системе управления версиями следующим образом (структура папок ниже):

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

[корень]
     [приложение]
         [версия]
             [сценарий]

\ scripts
    MyApplication \
        1.2.1 \
            001.MyTable.Create.sql
            002.MyOtherTable.Create.sql
            100.dbo.usp.MyTable.GetAllNewStuff.sql

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

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

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

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

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

Для любого нового проекта существует установленный порядок действий. У нас есть сценарий создания схемы в версии 1, сценарий создания хранимой процедуры и, возможно, сценарий создания начальной загрузки данных. Все процессы хранятся в одном массивном файле. Если мы используем Enterprise Library, мы включаем копию сценария создания для ведения журнала; если это проект ASP.NET, использующий платформу приложения ASP.NET (аутентификация, персонализация и т. д.), мы также включаем этот сценарий. (Мы создали его с помощью инструментов Microsoft, а затем настроили до тех пор, пока он не стал воспроизводимым на разных сайтах. Это не развлечение, но ценное вложение времени.)

Мы используем магию CTRL + F, чтобы найти понравившуюся процедуру. :) (Нам бы очень понравилось, если бы в SQL Management Studio была навигация по коду, как в VS. Вздох!)

Для последующих версий у нас обычно есть скрипты upgradeSchema, upgradeProc и / или updateDate. Для обновлений схемы мы, насколько это возможно, ИЗМЕНЯЕМ таблицы, создавая новые по мере необходимости. Для обновлений proc мы УДАЛЯЕМ и СОЗДАЕМ.

При таком подходе появляется одна морщинка. Создать базу данных легко, и легко получить новую в соответствии с текущей версией БД. Однако следует проявлять осторожность при генерации DAL (что мы в настоящее время - обычно - делаем с SubSonic), чтобы гарантировать, что изменения DB / schema / proc синхронизируются чисто с кодом, используемым для доступа к ним. Однако в наших путях сборки есть командный файл, который генерирует SubSonic DAL, поэтому наша СОП - проверить код DAL, повторно запустить этот командный файл, а затем снова проверить все это при изменении схемы и / или процессов. (Это, конечно, запускает сборку исходного кода, обновляя общие зависимости до соответствующих DLL ...)

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

Ниже приведен код VS2005, проект командной строки, вызываемый из пакетного файла, который выполняет всю работу. ключи app.config в конце кода.

Он основан на другом коде, который я нашел в Интернете. Немного сложно настроить, но работает хорошо, когда вы заставляете его работать.

Imports Microsoft.VisualStudio.SourceSafe.Interop
Imports System
Imports System.Configuration

Module Module1

    Dim sourcesafeDataBase As String, sourcesafeUserName As String, sourcesafePassword As String, sourcesafeProjectName As String, fileFolderName As String


    Sub Main()
        If My.Application.CommandLineArgs.Count > 0 Then
            GetSetup()
            For Each thisOption As String In My.Application.CommandLineArgs
                Select Case thisOption.ToUpper
                    Case "CHECKIN"
                        DoCheckIn()
                    Case "CHECKOUT"
                        DoCheckOut()
                    Case Else
                        DisplayUsage()
                End Select
            Next
        Else
            DisplayUsage()
        End If
    End Sub

    Sub DisplayUsage()
        Console.Write(System.Environment.NewLine + "Usage: SourceSafeUpdater option" + System.Environment.NewLine + _
            "CheckIn - Check in ( and adds any new ) files in the directory specified in .config" + System.Environment.NewLine + _
            "CheckOut - Check out all files in the directory specified in .config" + System.Environment.NewLine + System.Environment.NewLine)
    End Sub

    Sub AddNewItems()
        Dim db As New VSSDatabase
        db.Open(sourcesafeDataBase, sourcesafeUserName, sourcesafePassword)
        Dim Proj As VSSItem
        Dim Flags As Integer = VSSFlags.VSSFLAG_DELTAYES + VSSFlags.VSSFLAG_RECURSYES + VSSFlags.VSSFLAG_DELNO
        Try
            Proj = db.VSSItem(sourcesafeProjectName, False)
            Proj.Add(fileFolderName, "", Flags)
        Catch ex As Exception
            If Not ex.Message.ToString.ToLower.IndexOf("already exists") > 0 Then
                Console.Write(ex.Message)
            End If
        End Try
        Proj = Nothing
        db = Nothing
    End Sub

    Sub DoCheckIn()
        AddNewItems()
        Dim db As New VSSDatabase
        db.Open(sourcesafeDataBase, sourcesafeUserName, sourcesafePassword)
        Dim Proj As VSSItem
        Dim Flags As Integer = VSSFlags.VSSFLAG_DELTAYES + VSSFlags.VSSFLAG_UPDUPDATE + VSSFlags.VSSFLAG_FORCEDIRYES + VSSFlags.VSSFLAG_RECURSYES
        Proj = db.VSSItem(sourcesafeProjectName, False)
        Proj.Checkin("", fileFolderName, Flags)
        Dim File As String
        For Each File In My.Computer.FileSystem.GetFiles(fileFolderName)
            Try
                Proj.Add(fileFolderName + File)
            Catch ex As Exception
                If Not ex.Message.ToString.ToLower.IndexOf("access code") > 0 Then
                    Console.Write(ex.Message)
                End If
            End Try
        Next
        Proj = Nothing
        db = Nothing
    End Sub

    Sub DoCheckOut()
        Dim db As New VSSDatabase
        db.Open(sourcesafeDataBase, sourcesafeUserName, sourcesafePassword)
        Dim Proj As VSSItem
        Dim Flags As Integer = VSSFlags.VSSFLAG_REPREPLACE + VSSFlags.VSSFLAG_RECURSYES
        Proj = db.VSSItem(sourcesafeProjectName, False)
        Proj.Checkout("", fileFolderName, Flags)
        Proj = Nothing
        db = Nothing
    End Sub

    Sub GetSetup()
        sourcesafeDataBase = ConfigurationManager.AppSettings("sourcesafeDataBase")
        sourcesafeUserName = ConfigurationManager.AppSettings("sourcesafeUserName")
        sourcesafePassword = ConfigurationManager.AppSettings("sourcesafePassword")
        sourcesafeProjectName = ConfigurationManager.AppSettings("sourcesafeProjectName")
        fileFolderName = ConfigurationManager.AppSettings("fileFolderName")

    End Sub

End Module



<add key = "sourcesafeDataBase" value = "C:\wherever\srcsafe.ini"/>
<add key = "sourcesafeUserName" value = "vssautomateuserid"/>
<add key = "sourcesafePassword" value = "pw"/>
<add key = "sourcesafeProjectName" value = "$/where/you/want/it"/>
<add key = "fileFolderName" value = "d:\yourdirstructure"/>

При работе со сценариями перетаскивания / создания в SQL Server следует иметь в виду, что разрешения на уровне объекта будут потеряны. Мы изменили наш стандарт, чтобы вместо этого использовать сценарии ALTER, которые поддерживают эти разрешения.

Есть еще несколько предостережений, например, тот факт, что при удалении объекта удаляются записи зависимостей, используемые процедурой sp_depends, а создание объекта создает только зависимости для этого объекта. Поэтому, если вы отбросите / создадите представление, sp_depends больше не будет знать никаких объектов, ссылающихся на это представление.

Мораль рассказа, используйте скрипты ALTER.

Хранимые процедуры получают по одному файлу на sp со стандартными операторами drop / create наверху. Представления и функции также получают свои собственные файлы, поэтому их легче редактировать и повторно использовать.

Схема - это для начала всего 1 скрипт, затем мы внесем изменения в версию.

Все это хранится в проекте базы данных Visual Studio, подключенном к TFS (@ work или VisualSVN Server @ home для личных вещей) со следующей структурой папок:
- проект
- функции
- schema
- хранимые процедуры
- просмотров

В моей компании мы, как правило, храним все элементы базы данных в системе управления версиями как отдельные сценарии, как и для отдельных файлов кода. Любые обновления сначала вносятся в базу данных, а затем переносятся в репозиторий исходного кода, поэтому сохраняется история изменений. На втором этапе все изменения базы данных переносятся в базу данных интеграции. Эта база данных интеграции точно отражает то, как должна выглядеть производственная база данных после развертывания. У нас также есть база данных QA, которая представляет текущее состояние производства (или последнее развертывание). После внесения всех изменений в базу данных интеграции мы используем инструмент сравнения схем (Red Gate SQL Diff для SQL Server), чтобы сгенерировать сценарий, который перенесет все изменения из одной базы данных в другую. Мы обнаружили, что это довольно эффективно, поскольку генерирует единый сценарий, который мы можем легко интегрировать с нашими установщиками. Самая большая проблема, с которой мы часто сталкиваемся, заключается в том, что разработчики забывают перенести свои изменения в интеграцию.

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

Чтобы «навязать» это моим командам, наши решения поддерживают как минимум один проект базы данных из Visual Studio Team Edition для специалистов по базам данных. Как и в случае с другими проектами в решении, проект базы данных получает управление версиями. Разбиение всего в базе данных на обслуживаемые части становится естественным процессом разработки, «дисциплинируя» мою команду на этом пути.

Конечно, будучи проектом Visual Studio, он далеко не идеален. Вы можете столкнуться с множеством причуд, которые могут вас расстроить или запутать. Требуется некоторое понимание того, как работает проект, прежде чем заставить его выполнять свои задачи. Примеры включают

• развертывание данных из файлов CSV.
• выборочное развертывание тестовых данных в зависимости от типа сборки.
• Сбой Visual Studio при сравнении с базами данных с определенным типом сборки CLR, встроенной в.
• нет средств различения между тестовыми / производственными базами данных, реализующими разные схемы аутентификации - пользователи SQL и пользователи Active Directory.

Но для команд, у которых нет практики управления версиями своих объектов базы данных, это хорошее начало. Другой известной альтернативой, конечно же, является Набор продуктов SQL Server от Red Gate, который большинство людей, использующих их, считают более совершенным, чем предложение Microsoft.

Я обнаружил, что самый простой, быстрый и безопасный способ сделать это - просто укусить пулю и использовать SQL Source Control от RedGate. Сценарий и хранение в репозитории за считанные минуты. Я просто хочу, чтобы RedGate смотрел на этот продукт как на лидера убытков, чтобы он получил более широкое распространение.

Если вы ищете простое и готовое решение, наша система SQL Historian использует фоновый процесс для автоматической синхронизации изменений DDL с TFS или SVN, прозрачным для всех, кто вносит изменения в базу данных. По моему опыту, большая проблема заключается в том, чтобы сохранить код в системе контроля версий с учетом того, что было изменено на вашем сервере - и это потому, что обычно вам приходится полагаться на людей (даже разработчиков!), Чтобы изменить их рабочий процесс, и не забывайте проверять их изменения. после того, как они уже сделали это на сервере. Если возложить эту ношу на машину, всем станет легче.