Database reconstruction series (2)

Database reconstruction series (2)

 

In the previous article ,《Database reconstruction series (1) (see http://blog.csdn.net/chszs/archive/2010/02/27/5332408.aspx
).

4. Database reconstruction instance
The balance column actually describes the "Account" entity rather than the "customer" entity.
Therefore, you need to remove the "balance" column from the "customer" table and add it to the "Account" table.

The reconstruction iteration activities are as follows:

(1) verify whether database reconstruction is appropriate;
Does This refactoring make sense? Do changes need to be implemented now? Is it worth doing this?

(2) select the most appropriate database reconstruction technology;
What really needs to be reconstructed is "using a formal data source"

(3) make the original database schema obsolete;
A conversion period is required, that is, the period passed"

(4) pre-test, medium-test, and post-test;
To be able to easily verify that the database can still work with the application after the change, you must be confident to change the database schema. The only way to achieve this is to adopt the test-driven TDD development method.
In TDD mode, write a test and write enough code. Generally, the Data Definition Language DDL is used to complete the test. Continue to work in this way until the database reconstruction is fully implemented.

(4.1) Test the database schema;
Database restructuring affects database schema. Therefore, you need to write database-oriented tests. You can check the database schema in many ways:
· Stored procedures and triggers
· Reference integrity ri
· View definition
· Default Value
· Unchanged data
Database testing tools: dbunit, a tool for managing test data, sqlunit, and commercial tools for data testing.

(4.2) Test the database schema used by the application;

(4.3) verify the effectiveness of data migration;
Many database reconstruction techniques require the migration of source data, such as copying the data value Customer. balance to account. balance, and check that the correct balance of each customer is indeed copied.

(4.4) Test the external program code.

· Modify the database schema;

In the preceding example, you must add the account. Balance column and two triggers: synchronizeaccountbalance and synchronizecustomerbalance.
Complete the DDL code:
Alter table account add balance numeric;
Comment on account. Balance 'move of customer. Balance column, finaldate = 2006-06-14 ';
Create or replace trigger synchronizecustomerbalance
Before insert or update
On account
Reference old as old New as new
For each row
Declare
Begin
If: New. balance is not null then
Updatecustomerbalance;
End if
End;
/
Comment on synchronizecustomerbalance 'move of customer. Balance column to account,
Dropdate = 2006-06-14 ';
Create or replace trigger synchronizeaccountbalance
Before insert or update or delete
On customer
Reference old as old New as new
For each row
Declare
Begin
If deleting then
Deletecustomerifaccountnotfound;
End if
If (updating or inserting) then
If: New. balance is not null then
Updateaccountbalanceforcustomer;
End if;
End if;
End;
/
Comment on synchronizeaccountbalance 'move of customer. Balance column to account, dropdate = 2006-06-14'

Some small scripts are used for each refactoring because of simplicity, correctness, and version control.

An important aspect of restructuring is to ensure that the deployment of database schema changes complies with the company's database development guidelines.

· Migrate source data;
If you need to write a support document to describe a table, column, or stored procedure, You Need To refactor the schema to make it easier to understand.
Several instructions may be avoided after a simple renaming. The clearer the design, the fewer documents required.

· Modify the external access program;
When the database schema is changed, you often need to reconstruct the original external program.

· Run the regression test;
One part of implementing refactoring is to test it to make sure it works.

· Version control of work;
Place refactoring under the control of Configuration Management CM.

· Announced the restructuring.
We need to communicate with interested parties about the reconstruction of the database.
An important aspect of the announcement is the updating of relevant documents. You also need to update the database's physical data model PDM.
Note: you do not need to publish immature data models.

Database reconstruction process:

Summary: The database reconstruction studio completes the development sandbox, preferably by a developer and a DBA.