Shulou(Shulou.com)05/31 Report--

This article mainly explains the "database installation package and upgrade package script tool RedGate what are the use of", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "database installation package and upgrade package script tool RedGate what the use of" it!

In the process of product development and version update, the structure of the database will inevitably change all the time. In order to minimize the workload of upgrading, it is very important to design a good way to upgrade the database. When designing a database installation package, consider both how the default data is generated during a new installation and how the old data is migrated from the older version, if necessary.

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

I. Database installation or upgrade

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

If Red Gate finds that the target table does not exist in the older 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 this.

If the target table already exists, the original table will be updated, and special attention should be paid to how the structure of the table to be changed changes. For example:

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

Now, we want to add a ParameterType field to form a federated primary key with the UserId field:

At this point, if the old version of the database has data, the structural modification of the table will fail because the ParameterType is empty after adding new fields during the upgrade, so the installation package will make an error.

The solution is to add a default value to this field. The general practice is to add a Default Constraint constraint under Schema Objects-Tables-Contraints of the database project:

The copy code is as follows:

ALTER TABLE [TMS]. [UserParameters]

ADD CONSTRAINT [DF_UserParameters_Type]

DEFAULT Numisu'

FOR [ParameterType]

II. Pre-script and Post-script

Generally speaking, most of the structural changes of data tables can be done automatically by RedGate, and all we need to do is to set the default values. But there are other situations that need to be done by writing your own scripts, and here are a few examples.

1. Default data

The default data is added after the database is created. We can add a script called DefaultData.sql to Post-script, as an example:

The copy 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 in this script is that the database is not necessarily empty, it may be upgraded, so you need to determine if there is any data. In addition, when writing these scripts, it is best to put them in a transaction, and the uncommitted data can be uncommitted when the installation fails, so that the user can directly reinstall it after troubleshooting the problem.

two。 A field has changed

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

First of all, definitely can't put it in the Post-script. In the process of installing the database, the installer will try to change the field to int and add a foreign key constraint. If there is data in the database itself, it will cause the conversion to int to fail or the foreign key constraint is not established.

To do this, we can first look up the data in the Terminal table in Pre-script and update it:

The copy 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 @ defaultTerminalId NVARCHAR (64) = (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 still have to consider a variety of situations: if you are upgrading from an older version, then the type of the TerminalId field is varchar, which needs to be converted. If it is not (for example, a new installation; or if the previous version has been converted to int, the Pre-script will still be executed in the next version, so consider this situation as well).

In the script, update the TerminalId of the queried Terminal table directly to the Rating table and replace what cannot be found with the default value (int can be converted to varchar if the width is enough; in addition, the column can also be a NULL value). In this way, after the execution of the Pre-script, it is already the target value, and the remaining column type conversions and foreign key constraints can be handed over to Red Gate.

The UPDATE statement can also Join other tables, which is interesting. You can learn this statement.

3. A table has been deleted

If a table is not needed, Red Gate will delete it directly. But if the data is still needed (for example, stored in another system), use Pre-script to store the data somewhere else, otherwise there will be no tables and data before Post-script execution after installation.

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

You can specify the Schema type of Red Gate upgrade, for example, we only care about all the tables under TMS and ignore the tables under other schema directly. With this, you can move this data to dbo in Pre-script:

The copy code is as follows:

-- Backup UserCard data, so that 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

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

The use of this design should be able to deal with most cases, of course, we should try to be thoughtful when designing the structure of the database, so as not to frequently modify the structure of the data table resulting in a lot of Pre-script and Post-script. After confirming that some script is not needed, we can also delete it.

Thank you for your reading, the above is the content of "database installation package and upgrade package script tool RedGate". After the study of this article, I believe you have a deeper understanding of the use of database installation package and upgrade package script tool RedGate, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.