Transcript Рефакторинг баз данных

Database refactoring
For the beginning…
• Avoid overspecialization
Barrier
Application developer
Database developer
Communication
Cooperation
Exchange of experience
Developer
Developer
Definition
• Refactoring of databases is the database
schema change, improving its design, but not
affecting its behavioral and information
semantics.
• It contains both structural and functional
aspects
What can we refactor in DB?
DB keeps:
• Data (are kept according to the scheme)
• Stored code
The stored code doesn’t differ from any other code
(it should be tested)
The scheme of data (tables, indexes, etc.)
The availability of data complicates the
refactoring of the scheme!
Why should we refactor?
• Correct the inherited database
• Ensure the evolutionary development
• Prevent the redundant design (over-design)
Smells of bad code in database
All the smells of codes can be applied to the stored
code including:
•
•
•
•
•
Procedures-monsters
Spaghetti code
Code duplication
Oversaturation of the conditional operators
etc.
Smells characteristic for DB:
•
•
•
•
•
•
Table/column of wide destination
Redundant data (repeated in several tables)
Table with a huge number of columns/rows
Smart columns (xml)
Lack of limitations (lack of validation)
Fear of changes in data scheme or procedure
Fear of changes
•
•
•
•
•
The most «stinking» smell
Prevents the development
Reduces the effectiveness
Suggests an even bigger mess
Over time it becomes only worse
How to proceed?
• Create your sandbox of development
• Transfer changes in the sandbox of integration
• Introduce in production
Sandboxes
Frequent deployment
Controlled deployment
Highly controlled
deployment
The best case (the easiest)
Worst case (the most complex)
Trivial things
• Is it possible to rename a column in the database
without changing the hundreds of applications?
• If we can not make such a triviality as we can do
something really serious?
Unit tests in the database
• Is it too difficult?
• Isn’t there a good testing tool (framework)?
Running unit tests
• Anonymous PL/SQL code
• No need to change the database
• Call raise_application_error a specific
communication in case of an error
• A rollback at the end of the test
• Launch any SQL tools
Example of a test
Logs changes (changelog)
• You must track changes
• Writing delta-scripts (migration):
▫ At the beginning of the transition period
▫ At the end of the transition period
• The same scripts for
▫ Update sandboxes
▫ Deployment on the production
Simple script
Versioning
Teamwork
• Developers
▫ Close cooperation with DB administrators
▫ Skills of operation with databases
• Administrators of DB and designers of DB
▫ Should be involved in developing the application
▫ Skills of application development
Testing tools
• Delta-scripts
▫ Dbdeploy, liquibase, deltasql
▫ It's easy to write yourself
• PL/SQL code, Oracle SQL Devepoler, Intellij
IDEA
Categories of database refactorings
Category
Description
Example
Refactoring of operation
structures
Changes in the definition Move a column from one
of one or more tables
table to another, or
break the multipurpose
column on the
several separate
columns, each of which
performs the individual
assignment
Refactorings of data
quality
Changes that can
improve the quality of
information stored in the
database
Creating a column that
does not allow NULL
values, to ensure that
there are always
meaningful values
Categories of database refactorings
Category
Description
Example
Refactorings of
referential
integrity
The changes, which ensure
that the referenced string
exists in another table, and
allow properly removing this
string which are not needed
Adding a trigger to realize the rule
of a cascading delete, covering the
two entities, to replace the code
which before was implemented
outside the database
Refactorings of
architecture
Changes contributing to the
improvement of interaction
between external programs
with database
Replacement of the existing
procedures in the Java language
which code is in a shared library
code, with the stored procedure,
which are in the database. After
the code is implemented in the
form of stored procedures, you can
use this code in an application
other than Java
Categories of database refactorings
Category
Description
Example
Refactoring of methods
Changes in the method
(stored procedure,
stored function or
trigger), which
contribute to the
improvement of its
quality. To the methods
of the database we can
apply many of the
refactorings of code
Rename the stored
procedure in order to
simplify the
understanding of its
purpose
Conversion other than
refactorings
Changes in the database
schema, which lead to
change in its semantics
Adding a new column to
an existing table