Database Installation Package and upgrade package script tool RedGate usage introduction, installation package redgate

Database Installation Package and upgrade package script tool RedGate usage introduction, installation package redgate

This log records the design of the database installation package I learned at the company. I have encountered some problems in my recent work. I 'd like to record and share these questions here.

During product development and version update, the database structure will inevitably change. To minimize the workload during the upgrade, it is important to design a good Database Upgrade Method. When designing the database installation package, you must consider how to generate default data during the new installation, as well as how to migrate the old data when upgrading from the old version if necessary ).

Basically, the installation package can be divided into three parts: Pre-script, database installation or upgrade, and Post-script.

1. database installation or upgrade

First, we use the Red Gate tool. This tool automatically compares the structural differences between the existing database and the target database, and generates a script for upgrade (in fact, it executes a series of SQL statements ). This is a good tool. It is recommended to use (as if you want to collect money), which can reduce the workload.

If Red Gate finds that the target table does not exist in the old database version, it automatically creates the table and sets the primary key, foreign key, and other constraints. There is nothing to say.

If the target table already exists, the original table will be updated. Pay special attention to the changes to the table structure. For example:

We originally had a UserParameter table with the following structure:

Now, we want to add a ParameterType field to form a joint primary key with The UserId field:

In this case, if there is data in the old version of the database, adding a new field during the upgrade will cause the structure modification of the table to fail because the ParameterType is empty. In this case, the installation package will fail.

The solution is to add a default value for this field. The general practice is to add a Default Constraint under Schema Objects-Tables-Contraints of the database project:
The Code is as follows:
Alter table [TMS]. [UserParameters]
Add constraint [DF_UserParameters_Type]
Default n 'su'
FOR [ParameterType]

Ii. Pre-script and Post-script

Generally, the structure changes of most data tables can be automatically completed by RedGate. All we need to do is set the default value. However, in other cases, you need to write your own scripts. Here are some 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, for example:
The Code is 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 about this script is that the database is not necessarily empty and may be upgraded. Therefore, you need to determine whether there is data. In addition, it is best to write these scripts in a transaction. When the installation fails, uncommitted data can be removed, so that the user can directly reinstall the installation after troubleshooting the problem.

2. A field has changed.

For example, we have a Rating table with a TerminalId field, which is originally of the VARCHAR type and records the machine name. Now our new version changes the field type to the int type, and adds a foreign key constraint associated with the Terminal table. In this case, we need to write a script ourselves.

First, it must not be placed in Post-script. During database installation, the installer will try to change the field to the int type and add the foreign key constraint. If the database contains data, the conversion to int fails or the foreign key constraint is invalid.

To this end, we can first find and update the data in the Terminal table in the Pre-script:
The Code is 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 @ defaterminterminalid NVARCHAR (64) = (select top 1 TerminalId from tms. Terminal order by TerminalId ASC)
 
UPDATE r
SET r. TerminalId = ISNULL (t. TerminalId, @ defaterminterminalid)
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

At first, we need to consider multiple situations: If the upgrade is from the old version, the TerminalId field type is varchar, and the conversion is required. If it is not (for example, a new installation or the previous version has been converted to an int, the Pre-script will still be executed in the next version, so you should also consider this situation), the conversion will be performed.

In the script, update the TerminalId of the queried Terminal table to the Rating table directly, and replace the unfound TerminalId with the default value (int can be converted to varchar, if the width is sufficient; in addition, this column can also be a NULL value ). In this way, the Pre-script is already the target value, and the type conversion and foreign key constraints of the remaining columns are handed over to Red Gate.

The UPDATE statement can also Join other tables, which is very interesting. You can take a look at this statement.

3. A table has been deleted.

If you do not need a table, Red Gate will delete it directly. However, if the data needs to be stored in another system, you need to use Pre-script to store the data elsewhere, otherwise, no table or data is available before the Post-script execution.

For example, we have a UserCard table. In the new version, the data is handled by another system. Therefore, we need to transfer the data to another system.

You can specify the Schema type of the Red Gate upgrade. For example, we only use all the tables in TMS and ignore the tables in other schemas. With this, you can move the data to dbo in the Pre-script:
The Code is as follows:
-- Backup UserCard data, so that we cocould 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

In this way, after the database is installed, the data is in the dbo. TempUserCard table. In this case, these tables can be transferred in the installation program of other components, Post-script, or other systems.

This design should be able to cope with most situations. Of course, we should consider the database structure as much as possible, to avoid frequent changes to the structure of the data table, the Pre-script and Post-script are too many and messy. You can delete a script if it is not used.