Banco de Dados Evolutivo – Parte 13

Nesse post da série Banco de Dados Evolutivo você aprenderá a criar o XML com a bundled change SQL para executar comandos mais complexos em sua base de dados através da ferramenta de versionamento de banco de dados Liquibase.


Agora que você já está familiarizado com os comandos e com o funcionamento do Liquibase irá finalmente executar uma mudança significativa na estrutura e nos dados do nosso sistema de vendas da Adventure Works.

Você irá desnormalizar a base de dados passando a guardar os dados de tipo de telefone da tabela Person.PhoneNumberType dentro da tabela Person.PersonPhone. Para isso, precisaremos remodelar a base de dados. Veja na Figura 1 o modelo MER atual da base AdventureWorks_Homolog.

Figura 1. Modelo MER atual da base AdventureWorks_Homolog.
Figura 1. Modelo MER atual da base AdventureWorks_Homolog.

Agora acompanhe na Figura 2 como ela ficará após a alteração.

Figura 2. Modelo MER da base de dados AdventureWorks_Homolog após a alteração.
Figura 2. Modelo MER da base de dados AdventureWorks_Homolog após a alteração.

O Liquibase não possui uma Bundled Change para executar uma modificação tão complexa, porém, disponibiliza uma refatoração de nome SQL para que isso seja possível (como fora dito anteriormente na Parte 9 do conjunto de posts). Essa mudança permite que qualquer comando SQL – seja a nivel de DDL, DML ou DCL – seja executado através do Liquibase.

Usaremos o código da Listagem 1 dentro do arquivo XML de nome changelog_1.xml que deverá ser salvo na pasta C:\changelog\versao_2.0. Você dará esse nome para o arquivo XML porque se trata da primeira refatoração da Versão 2 do sistema.

<databaseChangeLog
       xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
       xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
       http://www.liquibase.org/xml/ns/dbchangelog-ext
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<changeSet author="arthur" id="exclui tabela Person.PhoneNumberType">
<sql> 	

-- Adiciona a Coluna PhoneNumberType à tabela Person.PersonPhone
ALTER TABLE Person.PersonPhone
ADD PhoneNumberType VARCHAR(25)
GO

-- Guarda os dados de tipo de telefone na tabela Person.PersonPhone
UPDATE Person.PersonPhone
SET PhoneNumberType = (	SELECT Name
			FROM Person.PhoneNumberType
			WHERE Person.PhoneNumberType.PhoneNumberTypeID = Person.PersonPhone.PhoneNumberTypeID)
GO

-- Exclui a Primary Key da tabela Person.PersonPhone
ALTER TABLE Person.PersonPhone
DROP CONSTRAINT PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID
GO 

-- Exclui a Foreign Key da tabela Person.PersonPhone
ALTER TABLE Person.PersonPhone
DROP CONSTRAINT FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID
GO

-- Exclui a coluna PhoneNumberTypeID da tabela Person.PersonPhone
ALTER TABLE Person.PersonPhone
DROP COLUMN PhoneNumberTypeID
GO 

-- Exclui a tabela Person.PhoneNumberType
DROP TABLE Person.PhoneNumberType
GO	

</sql>
<rollback>   

-- Cria a tabela Person.PhoneNumberType
CREATE TABLE Person.PhoneNumberType (
	PhoneNumberTypeID INT PRIMARY KEY IDENTITY NOT NULL,
	Name VARCHAR (25) NOT NULL,
	ModifiedDate DATETIME
	)
GO

-- Insere os dados de tipo de telefone na tabela Person.PhoneNumberType
INSERT INTO Person.PhoneNumberType
SELECT DISTINCT	PhoneNumberType,
		GETDATE()
FROM Person.PersonPhone
GO

-- Adiciona a coluna PhoneNumberTypeID na tabela Person.PersonPhone
ALTER TABLE Person.PersonPhone
ADD PhoneNumberTypeID INT
GO

-- Adiciona a chave da tabela Person.PhoneNumberType na tabela Person.PersonPhone
UPDATE Person.PersonPhone
SET PhoneNumberTypeID = (	SELECT PhoneNumberTypeID
			FROM Person.PhoneNumberType
			WHERE Person.PhoneNumberType.Name = Person.PersonPhone.PhoneNumberType)
GO

-- Torna a coluna PhoneNumberTypeID NOT NULL
ALTER TABLE Person.PersonPhone
ALTER COLUMN PhoneNumberTypeID INT NOT NULL
GO

-- Adiciona a Foreign Key na tabela PersonPhone
ALTER TABLE Person.PersonPhone
WITH CHECK ADD CONSTRAINT FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID
FOREIGN KEY(PhoneNumberTypeID)
REFERENCES Person.PhoneNumberType (PhoneNumberTypeID)
GO

-- Habilita a Constraint FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID
ALTER TABLE Person.PersonPhone
CHECK CONSTRAINT FK_PersonPhone_PhoneNumberType_PhoneNumberTypeID
GO

-- Exclui a coluna que guarda o tipo de telefone da tabela Person.PersonPhone
ALTER TABLE Person.PersonPhone
DROP COLUMN PhoneNumberType
GO

-- Adiciona a Primary Key na tabela Person.PersonPhone
ALTER TABLE Person.PersonPhone
ADD CONSTRAINT PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID PRIMARY KEY CLUSTERED
(
	BusinessEntityID ASC,
	PhoneNumber ASC,
	PhoneNumberTypeID ASC
)WITH (	PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF,
		SORT_IN_TEMPDB = OFF,
		IGNORE_DUP_KEY = OFF,
		ONLINE = OFF,
		ALLOW_ROW_LOCKS = ON,
		ALLOW_PAGE_LOCKS = ON)
GO

</rollback>

</changeSet>
</databaseChangeLog>

Listagem 1. Comando usado para criação do arquivo XML changelog_1.xml. Observe a utilização da tag ROLLBACK. Ela é responsável por desfazer a alteração executada na bundled change SQL caso seja necessário.

Agora uma dica: é aconselhável que a tag <rollback> … </rollback> seja incluída em todas as refatorações que não possuam rollback automático (para mais informações sobre as refatorações que possuem rollback automático ou não, acesse a pagina principal de Bundled Changes no site oficial do Liquibase e selecione a refatoração desejada. Na parte Database Support, na coluna Auto Rollback é possível saber se aquela change possui ou não auto rollback para cada banco de dados).

Após concluída a criação do arquivo changelog_1.xml e de colocado na pasta versao_2.0, você precisará criar o arquivo master_2.0.xml e colocá-lo na pasta de mesmo nome dentro do diretório changelog. Use o comando da Listagem 2.

<databaseChangeLog
       xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
       xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
       http://www.liquibase.org/xml/ns/dbchangelog-ext
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<include file="C:/changelog/versao_2.0/changelog_1.xml" />

</databaseChangeLog>

Listagem 2. Comando usado dentro do arquivo XML master_2.0.xml.

Depois disso, altere o arquivo master.xml conforme a Listagem 3 para adicionar a execução do arquivo master_2.0.xml.

<databaseChangeLog
       xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
       xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
       http://www.liquibase.org/xml/ns/dbchangelog-ext
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<include file="C:/changelog/versao_1.0/master_1.0.xml" />
<include file="C:/changelog/versao_2.0/master_2.0.xml" />

</databaseChangeLog>

Listagem 3. Comando usado dentro do arquivo master.xml para adicionar a nova tag de include para o arquivo master_2.0.xml.

Agora você já sabe o que fazer. Basta executar o comando da Listagem 4 no cmd do windows (a partir diretório raiz do sistema). O Liquibase irá fazer o que chamamos de Migrate. Ele trabalhará a nível de DDL e DML para retirar os dados de tipo de telefone da tabela Person.PhoneNumberType e armazená-los na nova coluna PhoneNumberType na tabela Person.PersonPhone. Criará as foreign keys e constraints e depois excluirá a tabela Person.PhoneNumberType e também a coluna PhoneNumberTypeID da tabela Person.PersonPhone.

java -jar C:\liquibase-3.0.8-bin\liquibase.jar --defaultsFile=C:\liquibase-3.0.8-bin\liquibaseHomolog.properties update

Listagem 4. Comando usado no cmd para execução do comando UPDATE através do Liquibase em ambiente de Homologação.

Agora que o comando foi executado, faça um SELECT na tabela Person.PersonPhone da nossa base de homologação (conforme na Figura 3) e veja que a coluna que possuía os IDs dos tipos de telefone não mais existe. Ao invés disso temos a nova coluna de nome PhoneNumberType. Caso navegue nos objetos de banco poderá também ver que não mais existe a tabela Person.PhoneNumberType.

Figura 3. SELECT executado na tabela Person.PersonPhone após a refatoração.
Figura 3. SELECT executado na tabela Person.PersonPhone após a refatoração.

No próximo post da série você irá migrar a base de dados de Produção para a segunda versão do sistema e aprenderá a executar o comando ROLLBACK <versao>.

Anúncios

3 comentários sobre “Banco de Dados Evolutivo – Parte 13

  1. Tenha um ótimo dia de férias, Roberto! heheeh

    Não se arrependa não. Quando voltar continue de onde parou! 🙂
    Quanto as perguntas, não se preocupe. Todas elas são bem vindas. hehehe
    Obrigado novamente por estar acompanhando!

    Grande abraço!

    Curtir

  2. Pingback: Banco de Dados Evolutivo – Parte 14 | Arthur Luz | Data's Light

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s