Database installation package and upgrade pack scripting Tools Redgate Use Introduction _ Database Other

This log records the design of the database installation package that I learned in the company. These are just some of the things I've been working on recently, and I'm here to record and share them.

In the process of product development and version updating, the structure of database will inevitably change. In order to minimize the workload during the upgrade, it is important to design a good database upgrade method. When designing a database installation package, you need to consider how the default data is generated when the new installation is installed, as well as how the old data is migrated when the old version is upgraded, if necessary.

Basically, the installation package can be divided into three parts: pre-script, database installation or upgrades, and post-script.

First, database installation or upgrade

First, we are using the Red Gate tool. This tool automatically compares the structural differences between an existing database and a target database and automatically generates a script to upgrade (actually executing a series of SQL statements). This is a good tool, recommended (as if to collect money), can reduce a lot of work.

If Red Gate discovers that the target table does not exist in the previous version of the database, it automatically creates the table and sets the primary key, foreign key, and other constraints. There's nothing to say about it.

If the target table already exists, the existing table will be updated with special attention to how the table structure changes. As an example:

We used to have a userparameter table with the following structure:

Now, we want to add a parametertype field and make a joint primary key with the UserID field:

At this point, if the older version of the database has data, adding a new field during the upgrade will cause the table's structural modification to fail because the parametertype is empty, causing the installation package to go wrong.

The workaround is to add a default value for this field. The general practice is to add a DEFAULT constraint constraint under the schema objects–tables–contraints of the database project:

Copy Code code as follows:

ALTER TABLE [TMS]. [Userparameters]
ADD CONSTRAINT [Df_userparameters_type]
DEFAULT N ' SU '
for [ParameterType]

Ii. Pre-script and Post-script

In general, most of the data table structure changes can be redgate automatic completion, we have to do is to set a good default value. But there are other situations that need to be scripted to do this, and here are a few examples.

1. Default data
The default data is added after the database is created. We can add a script named Defaultdata.sql in Post-script, as follows:

Copy Code code as follows:

SET NOCOUNT on
SET Xact_abort on
BEGIN TRANSACTION
--New default for Flooralertorder
IF not EXISTS (SELECT 1 from tms.[ Flooralertorder] WHERE [Modeid] = 1 and [typeid] = 7)
INSERT into [TMS]. [Flooralertorder] ([typeID], [Ordinal], [Modeid]) VALUES (7, 10, 1)

--TMS. User

IF not EXISTS (SELECT 1 from [tms].[ User] where XRef = ' Host '
INSERT into [TMS]. [User]
([Active]
, [XRef]
, [LastName]
, [FirstName]
, [UserName]
, [CreationTime]
, [Dealer]
, [Casinohost]
, [DomainName]
, [Cmsusername])
VALUES
(1
, ' Host '
, ' Host '
, ' Host '
, ' Host '
, getUTCDate ()
, 0
, 0
, ' Host '
, ' Host ')
COMMIT TRANSACTION
Go

The only thing to consider for this script is that the database is not necessarily empty, it may be upgraded, so you need to decide if there is any data. In addition, the best place to write these scripts in the transaction, the installation failed to undo the uncommitted data, so that the user after troubleshooting the problem can be directly reinstalled again.

2. A field has changed

For example, we have a rating table, which has a terminalid field, the original is varchar type, the record is the machine name. Now our new version changes the type of the field to type int and adds a foreign key constraint associated with the terminal table. In this case, we need to write a script ourselves.

First of all must not be placed in the post-script. During the installation of the database, the installer attempts to change the field to an int type with a foreign key constraint, and if the database itself has data, it can cause the conversion to int failed or the FOREIGN KEY constraint is not valid.

To do this, we can find the data in the Pre-script and update it first in the terminal table:

Copy Code code as follows:

BEGIN TRANSACTION
BEGIN TRY

IF EXISTS (select 1 from INFORMATION_SCHEMA. COLUMNS WHERE table_name = ' Rating ' and column_name = ' terminalid ' and data_type = ' varchar ')
BEGIN

DECLARE @defaultTerminalId NVARCHAR = (SELECT top 1 terminalid from TMS. Terminal ORDER by Terminalid ASC)

UPDATE R
SET r.terminalid = ISNULL (T.terminalid, @defaultTerminalId)
From TMS. Rating R
Left JOIN TMS. Terminal T on r.terminalid = T.name

End
COMMIT TRANSACTION
End TRY

BEGIN CATCH
IF @ @TRANCOUNT > 0 ROLLBACK
End CATCH

Go

In the beginning, we have to take into account a variety of situations: if the upgrade from the old version, then the Terminalid field is the type of varchar, this time need to convert. If it is not (for example, a new installation, or if the previous version has been converted to int, the Pre-script will still execute when the next version is taken, so be it), and the conversion will take place.

In the script, the terminalid of the query to the terminal table is updated directly into the rating table, and the default value cannot be found instead (int can be converted to varchar if the width is sufficient; In addition, the column can also be a null value). In this way, after this pre-script is already the target value, the remaining column type conversion and FOREIGN key constraints are given to Red gate.

It is interesting that the UPDATE statement can join other tables, so you can learn this statement.

3. A table has been deleted

If a table is not needed, then red gate will simply erase it. But if the data is needed (for example, in another system), use Pre-script to save the data somewhere else, otherwise the table and the data will be gone before the Post-script executes.

For example, we have a usercard table, the new version of which is responsible for another system, so we need to transfer this data to another system.

You can specify the schema type of the red Gate upgrade, such as all of our tables under TMS, and ignore the tables in other schemas directly. With this, you can move the data to the DBO in Pre-script:

Copy Code code as follows:

--Backup Usercard data, so and we could transfer them to sbdb when installing TMS

BEGIN TRANSACTION
BEGIN TRY

IF EXISTS (SELECT * from INFORMATION_SCHEMA. TABLES WHERE table_name = ' tempusercard ' and table_schema = ' dbo '
DROP TABLE [dbo]. [Tempusercard]

CREATE TABLE [dbo]. [Tempusercard] (Usercardid BIGINT NOT NULL, UserId BIGINT NOT NULL, Cardinfo NVARCHAR (256) NOT NULL)

IF EXISTS (SELECT * from INFORMATION_SCHEMA. TABLES WHERE table_name = ' usercard ' and table_schema = ' TMS '
BEGIN

INSERT into [dbo]. [Tempusercard]
SELECT Usercardid, UserId, cardinfo from [TMS]. [Usercard]

End

COMMIT TRANSACTION
End TRY

BEGIN CATCH
IF @ @TRANCOUNT > 0 ROLLBACK
End CATCH

Go

This way, after the database is installed, the data is in dbo. Tempusercard the table. These tables can then be transferred to other components in the installer, or post-script, or other systems.

The use of this design should be able to deal with most of the situation, of course, we design the structure of the database should be considered as well as possible to avoid frequent changes in the structure of the datasheet caused Pre-script and post-script very much and chaos. We can also delete some script if it is not used.