Versionando una base de datos con ejemplos - Devart blog

Para administrar correctamente el proceso de desarrollo de la base de datos, estar al tanto de los cambios realizados por otros desarrolladores y evitar conflictos o pérdidas de datos en la base de datos, debe administrar las versiones de la base de datos. Esto le permite reconstruir cualquier versión de la base de datos, ver el historial de cambios y garantizar la entrega ininterrumpida de la base de datos.

Índice

    Control de versiones de la base de datos

    A diferencia de los archivos y proyectos, una base de datos consta de cuatro componentes principales:

    1. esquema de base de datos
    2. Definiciones de objetos de base de datos
    3. bases de datos de bases de datos
    4. Roles y privilegios de los usuarios

    Los scripts DDL de base de datos incluyen scripts DDL separados para esquemas de base de datos y objetos de base de datos. Contienen:

    • Definiciones de tabla y vista
    • Definiciones clave básicas
    • Definiciones de clave externa
    • Definiciones de procedimientos almacenados y funciones
    • Ejecuta definiciones
    • Definiciones de secuencia
    • Definiciones de índice
    • Definiciones de restricciones

    Tipos de control de versiones de bases de datos

    Por lo tanto, hay tres tipos principales de control de versiones de bases de datos:

    1. Base de datos DDL control de versiones (scripts DDL para esquemas de bases de datos y otros objetos de bases de datos)
    2. Base de datos datos versión
    3. Versionado roles y privilegios de usuario

    Cuando se trata de implementación, el punto tres es muy similar al punto uno. Así que consideremos un punto en profundidad.

    Versiones DDL

    Podemos implementar versiones DDL utilizando dos enfoques principales:

    1. Redefinir todos los objetos de la base de datos, excepto las tablas (las tablas se procesan por separado).
    2. Actualizar objetos modificados.

    Redefiniendo objetos

    El primer enfoque es simple pero inconveniente para cambiar mesas. Debe anular las tablas en una iteración separada, y esto afecta el tiempo de implementación. Sin embargo, este tipo se aplica al tipo de proyecto de base de datos de SQL Server en Visual Studio:

    Figura 1. Proyecto de base de datos

    Actualizar objetos modificados

    El segundo enfoque es complejo pero más eficaz. Esto le permite actualizar los objetos modificados de forma selectiva y no genera anulaciones falsas. Por lo tanto, reduce significativamente el tiempo de implementación.

    Hay muchas soluciones preparadas aplicables al primer enfoque. Dado que el segundo enfoque reduce significativamente el tiempo de implementación, considérelo con más detalle.

    Hay tres formas de grabar y comprobar las versiones:

    1. Uso de una base de datos y propiedades avanzadas de objetos
    2. Usando una tabla especial en la base de datos
    3. Uso de sumas de comprobación de bases de datos y definiciones de objetos.

    El tercer método no es tan común. Así que no consideraremos esto aquí. Los métodos uno y dos son más comunes; por ejemplo, el segundo se implementa en .NET Entity Framework. La esencia del segundo método es que las versiones de migración se almacenan en una tabla de base de datos especial.

    Ahora pasemos al primer método. Revisaremos el proceso de actualización de las definiciones de la base de datos actualizando las definiciones de aquellos objetos que han sido modificados y verificando y escribiendo sus versiones a través de sus propiedades avanzadas.

    Por lo general, primero determinamos el número de versión de compilación. La práctica común ofrece el siguiente formato:

    YYYY_NN para el problema donde NN - número de emisión en el año AAAA.

    YYYY_NN_HF_N para corregir dónde N es el número de revisión para el problema YYYY_NN

    Este formato de numeración es adecuado para problemas que no aparecen más de una vez por semana y no más de nueve arreglos por problema. Entonces podemos especificar el número de versión de la base de datos de la siguiente manera:

    declare @DB_Version nvarchar(128)=N'XXXX_NN'; --specify the release version here, for example, 2020_02 for the release version or 2020_01_HF_01 for the hotfix version 
    --the variable for the previous release value
    declare @DB_BackVersion nvarchar(128);
    SELECT TOP(1)
    @DB_BackVersion=cast([value] as nvarchar(128))
    FROM sys.extended_properties
    WHERE [class]=0
      AND [name]='DB_Version';
       
    IF(@DB_BackVersion is not null)
    BEGIN
        EXEC sys.sp_updateextendedproperty @name=N'DB_Version', @[email protected]_Version;
    END
    ELSE
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'DB_Version', @[email protected]_Version;
    END
      
    IF(
        EXISTS(
                SELECT TOP(1) 1
                FROM sys.extended_properties
                WHERE [class]=0
                  AND [name]='DB_BackVersion'
              )
      )
    BEGIN
        EXEC sys.sp_updateextendedproperty @name=N'DB_BackVersion', @[email protected]_BackVersion;
    END
    ELSE
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'DB_BackVersion', @[email protected]_BackVersion;
    END
    

    Nota: antes de actualizar la etiqueta DB_Version, el script guarda la etiqueta de la versión anterior en DB_BackVersion.

    Puede marcar cualquier objeto de la base de datos de forma similar.

    Puede usar el siguiente script para revertir la versión de la base de datos:

    --a variable that will contain the previous release number
    declare @DB_BackVersion nvarchar(128);
     
    SELECT TOP(1)
    @DB_BackVersion=cast([value] as nvarchar(128))
    FROM sys.extended_properties
    WHERE [class]=0
      AND [name]='DB_BackVersion';
     
    --if the previous version is not set, we initialize it as 2020_00, i.e. no versioning
    if(@DB_BackVersion is null) set @DB_BackVersion=N'2020_00';
     
    IF(
        EXISTS(
                SELECT TOP(1) 1
                FROM sys.extended_properties
                WHERE [class]=0
                  AND [name]='DB_Version'
              )
      )
    BEGIN
        EXEC sys.sp_updateextendedproperty @name=N'DB_Version', @[email protected]_BackVersion;
    END
    ELSE
    BEGIN
        EXEC sys.sp_addextendedproperty @name=N'DB_Version', @[email protected]_BackVersion;
    END
    

    Aquí, la etiqueta DB_Version obtiene el valor asignado de la etiqueta DB_BackVersion.

    De manera similar, podemos revertir la etiqueta de versión para cualquier objeto de la base de datos.

    Además, podemos desarrollar sistemas de control de versiones más sofisticados para mover las etiquetas de un lado a otro. Por ejemplo, puede recordar no solo el valor de una versión anterior, sino también almacenar más valores de etiqueta o incluso todos los valores de etiqueta.

    Sin embargo, normalmente es suficiente mantener las versiones actuales y anteriores de los objetos de la base de datos y la propia base de datos.

    Si tenemos las etiquetas de versión ubicadas en la base de datos, podemos verificar la definición del objeto de la base de datos para el valor de la versión actual. Según el valor obtenido, el sistema puede actualizar la definición del objeto o informar al usuario que la actualización no es posible.

    Por ejemplo, creó un procedimiento guardado para compilar 2020_03. Por lo tanto, puede avanzar esta definición de procedimiento almacenado solo si su versión en la base de datos de destino es anterior a 2020_03. Si redirige la definición de procedimiento guardada versión 2020_03 a la misma versión o a una más reciente, sobrescribirá la nueva definición de procedimiento. No sería una reversión, sería una reversión.

    Esto es lo mismo para revertir bases de datos y definiciones de objetos. Puede revertir la versión 2020_03 solo si el objeto está exactamente en la misma versión que la base de datos. De lo contrario, puede revertir la versión incorrecta porque el objeto y la base de datos están en una versión diferente a la prevista para la reversión.

    Resumiendo, revisamos los conceptos básicos de las versiones de bases de datos.

    Versiones de la base de datos

    Pero, ¿cómo podemos bases de datos de versiones? Aquí debemos considerar las siguientes comprobaciones:

    1. Consultar disponibilidad existencia de registro.
    2. Consultar disponibilidad requerido el valor del campo (s).

    En general, para agregar o cambiar una cadena, necesitamos escribir el siguiente pseudocódigo:

    ;MERGE <schema_name>.<table_name> AS trg
    USING #tbl_source AS src ON trg.[PK_1]=src.[PK_1] AND ... AND trg.[PK_N]=src.[PK_N]
    WHEN NOT MATCHED THEN
    	 INSERT ([PK_1], ..., [PK_N], [Field_1], ..., [Field_M])  
    	 VALUES (src.[PK_1], ..., src.[PK_N], src.[Field_1], ..., src.[Field_M])
    WHEN MATCHED AND ((((trg.[Field_1]<>src.[Field_1]) AND ((trg.[Field_1] IS NOT NULL) AND (src.[Field_1] IS NOT NULL))) OR ((trg.[Field_1] IS NULL) AND (trg.[Field_N] IS NOT NULL)) OR ((trg.[Field_1] IS NOT NULL) AND (trg.[Field_1] IS NULL)))
    					OR
    					...
    					OR (((trg.[Field_M]<>src.[Field_M]) AND ((trg.[Field_M] IS NOT NULL) AND (src.[Field_M] IS NOT NULL))) OR ((trg.[Field_M] IS NULL) AND (trg.[Field_M] IS NOT NULL)) OR ((trg.[Field_M] IS NOT NULL) AND (trg.[Field_M] IS NULL)))
    					)
    	 THEN UPDATE SET
    	 trg.[Field_1]=src.[Field_1],
    	 ...
    	 trg.[Field_M]=src.[Field_M];

    Aquí, en lugar de una tabla temporal #tbl_source, se puede usar cualquier fuente de datos que satisfaga los parámetros de salida. Estos parámetros deben coincidir con la tabla de destino. Por ejemplo, en lugar de la tabla temporal #tbl_source, podemos usar valores específicos para una o más filas, de la siguiente manera:

    ;MERGE <schema_name>.<table_name> AS trg
    USING (
    		SELECT <value_PK_1> AS [PK_1], ..., <value_PK_N> AS [PK_N], <value_Field_1> AS [Field_1], ..., <value_Field_M> AS [Field_M] UNION ALL
    		...
    		SELECT <value_PK_1> AS [PK_1], ..., <value_PK_N> AS [PK_N], <value_Field_1> AS [Field_1], ..., <value_Field_M> AS [Field_M]
    	  ) AS src ON trg.[PK_1]=src.[PK_1] AND ... AND trg.[PK_N]=src.[PK_N]
    WHEN NOT MATCHED THEN
    	 INSERT ([PK_1], ..., [PK_N], [Field_1], ..., [Field_M])  
    	 VALUES (src.[PK_1], ..., src.[PK_N], src.[Field_1], ..., src.[Field_M])
    WHEN MATCHED AND ((((trg.[Field_1]<>src.[Field_1]) AND ((trg.[Field_1] IS NOT NULL) AND (src.[Field_1] IS NOT NULL))) OR ((trg.[Field_1] IS NULL) AND (trg.[Field_N] IS NOT NULL)) OR ((trg.[Field_1] IS NOT NULL) AND (trg.[Field_1] IS NULL)))
    					OR
    					...
    					OR (((trg.[Field_M]<>src.[Field_M]) AND ((trg.[Field_M] IS NOT NULL) AND (src.[Field_M] IS NOT NULL))) OR ((trg.[Field_M] IS NULL) AND (trg.[Field_M] IS NOT NULL)) OR ((trg.[Field_M] IS NOT NULL) AND (trg.[Field_M] IS NULL)))
    					)
    	 THEN UPDATE SET
    	 trg.[Field_1]=src.[Field_1],
    	 ...
    	 trg.[Field_M]=src.[Field_M];

    Tomemos un ejemplo de [dbo].[Company] definición de tabla:

    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Company](
    	[CompanyID] [int] IDENTITY(1,1) NOT NULL,
    	[CompanyName] [nvarchar](255) NOT NULL,
    	[Description] [nvarchar](255) NOT NULL,
    	[IsDeleted] [bit] NOT NULL,
     CONSTRAINT [PK_Company_CompanyID] PRIMARY KEY CLUSTERED 
    (
    	[CompanyID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[Company] ADD  DEFAULT ((0)) FOR [IsDeleted]
    GO

    Para agregar y modificar datos de dos empresas, podemos escribir el siguiente fragmento de código:

    SET IDENTITY_INSERT [dbo].[Company] ON;
    
    ;MERGE [dbo].[Company] AS trg
    USING (
    		SELECT 1001 AS [CompanyID], 'Microsoft' AS [CompanyName], 'IT company' AS [Description], 0 AS [IsDeleted] UNION ALL
    		SELECT 10 AS [CompanyID], 'DSS' AS [CompanyName], 'IT company' AS [Description], 1 AS [IsDeleted]
    	  ) AS src ON trg.[CompanyID]=src.[CompanyID]
    WHEN NOT MATCHED THEN
    	 INSERT ([CompanyID], [CompanyName], [Description], [IsDeleted])  
    	 VALUES (src.[CompanyID], src.[CompanyName], src.[Description], src.[IsDeleted])
    WHEN MATCHED AND ((((trg.[CompanyName]<>src.[CompanyName]) AND ((trg.[CompanyName] IS NOT NULL) AND (src.[CompanyName] IS NOT NULL))) OR ((trg.[CompanyName] IS NULL) AND (trg.[CompanyName] IS NOT NULL)) OR ((trg.[CompanyName] IS NOT NULL) AND (trg.[CompanyName] IS NULL)))
    					OR
    				  (((trg.[Description]<>src.[Description]) AND ((trg.[Description] IS NOT NULL) AND (src.[Description] IS NOT NULL))) OR ((trg.[Description] IS NULL) AND (trg.[Description] IS NOT NULL)) OR ((trg.[Description] IS NOT NULL) AND (trg.[Description] IS NULL)))
    				  OR
    				  (((trg.[IsDeleted]<>src.[IsDeleted]) AND ((trg.[IsDeleted] IS NOT NULL) AND (src.[IsDeleted] IS NOT NULL))) OR ((trg.[IsDeleted] IS NULL) AND (trg.[IsDeleted] IS NOT NULL)) OR ((trg.[IsDeleted] IS NOT NULL) AND (trg.[IsDeleted] IS NULL)))
    					)
    	 THEN UPDATE SET
    	 trg.[CompanyName]=src.[CompanyName],
    	 trg.[Description]=src.[Description],
    	 trg.[IsDeleted]=src.[IsDeleted];
    
    SET IDENTITY_INSERT [dbo].[Company] OFF;

    Nota: Debido a que CompanyID es una columna de IDENTIDAD, debe incluir el permiso de inserción para esa columna en la tabla de la empresa antes de pegar o fusionar. Luego deberá deshabilitar esta opción usando la siguiente declaración: SET IDENTITY_INSERT <імя_схемы>.<імя_табліцы> .

    Una simple consulta SELECT prueba que nuestra tabla ahora contiene los valores que insertamos usando la instrucción MERGE:

    Figura 2. Datos agregados y modificados en la tabla de empresas

    Puede realizar la eliminación de la misma manera:

    ;MERGE <schema_name>.<table_name> AS trg
    USING #tbl_source AS src ON trg.[PK_1]=src.[PK_1] AND ... AND trg.[PK_N]=src.[PK_N]
    WHEN MATCHED DELETE;

    Donde, en lugar de la tabla temporal #tbl_source, se puede usar cualquier fuente de datos que satisfaga el parámetro de salida. Estos parámetros deben coincidir con la tabla de destino. Por ejemplo, en lugar de la tabla temporal #tbl_source, podemos usar valores específicos para una o más filas, de la siguiente manera:

    ;MERGE <schema_name>.<table_name> AS trg
    USING (
    		SELECT <value_PK_1> AS [PK_1], ..., <value_PK_N> AS [PK_N], <value_Field_1> AS [Field_1], ..., <value_Field_M> AS [Field_M] UNION ALL
    		...
    		SELECT <value_PK_1> AS [PK_1], ..., <value_PK_N> AS [PK_N], <value_Field_1> AS [Field_1], ..., <value_Field_M> AS [Field_M]
    	  ) AS src ON trg.[PK_1]=src.[PK_1] AND ... AND trg.[PK_N]=src.[PK_N]
    WHEN MATCHED DELETE;

    Por ejemplo, para eliminar dos empresas de nuestra tabla, debemos escribir el siguiente fragmento de código:

    ;MERGE [dbo].[Company] AS trg
    USING (
    		SELECT 1001 AS [CompanyID], 'Microsoft' AS [CompanyName], 'IT company' AS [Description], 0 AS [IsDeleted] UNION ALL
    		SELECT 1002 AS [CompanyID], 'NPP' AS [CompanyName], 'IT company' AS [Description], 1 AS [IsDeleted]
    	  ) AS src ON trg.[CompanyID]=src.[CompanyID]
    WHEN MATCHED THEN DELETE;

    Para asegurarnos de que nuestros esfuerzos fueron exitosos y los registros fueron eliminados, podemos completar la siguiente consulta:

    Figura 3. Resultado de la eliminación de datos

    En todas las situaciones mencionadas anteriormente, agregamos, modificamos y eliminamos elementos mediante la instrucción MERGE. De la misma manera podemos usar el script en ejemplos más complejos.

    Por lo general, la versión de las versiones de datos se aplica a enlaces e información reglamentaria. El código de datos en sí está controlado por versiones utilizando los tres sistemas principales de control de versiones que discutimos anteriormente:

    1. Flujo Git
    2. Flujo de GitHub
    3. Flujo de GitLab

    En resumen, revisamos los enfoques básicos para la gestión de versiones de la base de datos y demostramos cómo controlar las versiones de los objetos de la base de datos, incluido el esquema de la base de datos y los datos de la base de datos.

    Si está buscando una solución robusta que admita todas las etapas del ciclo de vida de la base de datos y garantice compilaciones y versiones de bases de datos consistentes, dbForge Source Control es una herramienta que lo ayudará a lograrlo. Este útil complemento de SSMS para bases de datos de SQL Server ha sido diseñado para ayudarlo a administrar las versiones de su base de datos.

    Con la herramienta, puede realizar y deshacer cambios, así como ver conflictos y resolver cualquier inconsistencia que surja. Además, obtiene la capacidad de automatizar el desarrollo de bases de datos utilizando el enfoque DevOps.

    Artículos de interés

    Subir