mysql - 将 SQL 更改部署到客户数据库(更改表、函数等)

Question

我正在开发一个在客户系统上使用数据库(PostgreSQL、MySQL、Oracle 或 MSSQL)的应用程序。

因此我需要对每个新版本执行数据库更新。

我目前处于概念阶段，没有任何东西投入生产。

所有的 DDL 语句都在脚本文件中。

结构如下:

tables\employees.sql
       customers.sql
       orders.sql

这些脚本也在版本控制中，可用于从stretch构建数据库。

当然，这些表格在未来的某个时候会有变化。

例如表 employees 是这样创建的:

CREATE TABLE if not exists employees
(
    EmployeeId      serial,
    FirstName       text,

    PRIMARY KEY (EmployeeId)
);

在未来的版本中，该表会得到扩展:

ALTER TABLE employees ADD COLUMN address varchar(30);

在我的研究中，我发现了这个例子:https://stackoverflow.com/posts/115422/revisions .版本号用于执行特定更改。

我喜欢这个概念，我的想法是实现类似的东西。但我考虑的不是系统版本号，而是为每个表引入一个版本。

当创建 employee 表时，它获得版本号 1。随着对该表的每次更改，版本号都会增加 1。添加 address 列后(更改上面的语句)表版本为 2。

每个表更改都将在嵌套事务中发生，如下所示:

BEGIN TRANSACTION;

UPDATE employees SET Version = 2;

ALTER TABLE employees
     ALTER TABLE employees ADD COLUMN address varchar(30);

END TRANSACTION;

如果表版本低于当前表版本，事务将被回滚。该逻辑的实现尚未完成。

好处是表上的所有更改都在表的脚本文件本身内，并且初始语句始终是最新的。

例如，当第一次创建 employee 表时，它看起来像这样:

employees.sql

CREATE TABLE if not exists employees
(
    EmployeeId      serial,
    FirstName       text,

    Version         int       default 1 not null,

    PRIMARY KEY (EmployeeId)
);

经过一些修改后，它看起来像这样:

employees.sql

CREATE TABLE if not exists employees
(
    EmployeeId      serial,
    FirstName       varchar(100),
    address         varchar(80),

    Version         int       default 3 not null, -- notice the 3 

    PRIMARY KEY (EmployeeId)
);

-- First Change
BEGIN TRANSACTION;

UPDATE employees SET Version = 2;

ALTER TABLE employees
    ALTER TABLE employees ADD COLUMN address varchar(30);

END TRANSACTION;

-- Second Change
BEGIN TRANSACTION;

UPDATE employees SET Version = 3;

ALTER TABLE employees
    ALTER COLUMN address TYPE varchar(80),
    ALTER COLUMN FirstName TYPE varchar(100);

END TRANSACTION;

这个概念可以接受还是我在这里重新发明轮子？

Answer 1

我认为为每个表设置版本号有点矫枉过正。此外，它使管理数据库和应用程序变得复杂。我建议您为 DB_VersionNumber 添加一个新表，并为每次升级在该表中添加一行。我一直在做的是:1)在数据库中为数据库版本创建一个表(步骤)2)创建一个检查这个表的SP，如果表中不存在则运行DB升级步骤，否则跳过该步骤。3) 对于每个数据库更改，在升级脚本文件(您已经创建并添加到源代码管理)中添加一个步骤。

Here is the table and the SP:

IF OBJECT_ID (N'DB_Version', N'U') IS  NULL
Begin
    CREATE TABLE [DB_Version](
        [VersionNumber] [decimal](18, 2) NOT NULL,
        [CommitTimestamp] [smalldatetime] NOT NULL
    ) ON [PRIMARY]


    ALTER TABLE DB_Version
    ADD CONSTRAINT UQ_VersionNumber UNIQUE (VersionNumber); 
End

IF OBJECT_ID ( 'NewDBStep', 'P' ) IS NULL 
begin
    Exec ('
    -- ============================================
    -- Description: Applies a new DB upgrade step to the current DB
    -- =============================================
    CREATE PROCEDURE NewDBStep 
        @dbVersion [decimal](18, 2),
        @script    varchar (max)
    AS
    BEGIN
        If not exists (select 1 from DB_Version Where VersionNumber = @dbVersion)
        Begin
            -- SET NOCOUNT ON added to prevent extra result sets from
            -- interfering with SELECT statements.
            SET NOCOUNT ON;

            BEGIN TRY
                Begin tran
                Exec (@script)

                Insert into DB_Version (VersionNumber, CommitTimestamp) Values (@dbVersion, CURRENT_TIMESTAMP);
                Commit tran

                Print ''Applied upgrade step '' + Cast ( @dbVersion as nvarchar(20))
            END TRY
            BEGIN CATCH
                Rollback tran
                Print ''Failed to apply step '' + Cast ( @dbVersion as nvarchar(20))
                Select ERROR_NUMBER() AS ErrorNumber
                ,ERROR_SEVERITY() AS ErrorSeverity
                ,ERROR_STATE() AS ErrorState
                ,ERROR_PROCEDURE() AS ErrorProcedure
                ,ERROR_LINE() AS ErrorLine
                ,ERROR_MESSAGE() AS ErrorMessage;
            END CATCH
        End
    END ') ;
End

然后，通过调用 SP 应用您的升级(关键是您必须为每个升级脚本分配一个唯一的步骤编号:

----------------  Add the new steps here
-- Step: 0.01 
-- Adding the MyTableName table if it does not exist. 
Exec NewDBStep 0.01, '
IF OBJECT_ID (N''MyTableName'', N''U'') IS  NULL
Begin
    CREATE TABLE [MyTableName](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [UserType] [nvarchar](20) NULL,
    PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
End
'
Exec NewDBStep 1.00, '
-- Some other DDL script 
'