Catalog of Database Refactorings: Method Refactorings (original) (raw)

by Scott W. Ambler

A method database refactoring is a change which improves the quality of a stored procedure, stored function, or trigger so as to improve your database design without changing its semantics.

Refactoring	Example
Add Parameter. An existing method needs information that was not passed in before.
Consolidate Conditional Expression. Combine sequence of conditional tests into a single conditional expression and extract it.	Before After CREATE OR REPLACE FUNCTION GetAccountAverageBalance( inAccountID IN NUMBER)RETURN NUMBER;ASaverageBalance := 0;BEGINIF inAccountID > 10000 THENRETURN 0;END IF;IF inAccountID = 123456 THENRETURN 0;END IF;IF inAcountID = 987654 THENRETURN 0;END IF;– Code to calculate the average balanceRETURN averageBalance;END; CREATE OR REPLACE FUNCTION GetAccountAverageBalance( inAccountID IN NUMBER)RETURN NUMBER;ASaverageBalance := 0;BEGINIF inAccountID < 10000 \|	inAccountID = 123456
Decompose Conditional. Extract methods from the condition.	Before After CREATE OR REPLACE FUNCTION CalculateInterest( inBalance IN NUMBER )RETURN NUMBER;ASlowBalance NUMBER;highBalance NUMBER;lowInterestRate NUMBER;highInterestRate NUMBER;BEGINlowBalance := GetLowBalance();highBalance := GetHighBalance();lowInterestRate := GetLowInterestRate();highInterestRate := GetHighInterestRate();IF inBalance < lowBalance THENRETURN 0;END IFIF inBalance >= lowBalance && inBalance <= highBalance THENRETURN inBalance * lowInterestRate;ELSERETURN inBalance * highInterestRate;END IF;END; CREATE OR REPLACE FUNCTION CalculateInterest( inBalance IN NUMBER )RETURN NUMBER;ASBEGINIF BalanceIsInsufficient( inBalance ) THENRETURN 0;END IFIF IsLowInterestBalance( inBalance ) THENRETURN CalculateLowInterest( inBalance );ELSERETURN CalculateHighInterest( inBalance );END IF;END;
Extract Method. Turn the code fragment into a method whose name explains the purpose of the method.	Before After CREATE OR REPLACE FUNCTION CalculateAccountInterest( inAccountID IN NUMBER,inStart IN DATE,inEnd IN DATE )RETURN NUMBER;ASmedianBalance NUMBER;startBalance NUMBER;endBalance NUMBER;interest := 0;BEGINBEGIN— Determine the starting balanceSELECT Balance INTO startBalanceFROM DailyEndBalanceWHERE AccountID = inAccountID && PostingDate = inStart;EXCEPTION WHEN NO_DATA_FOUND THENstartBalance := 0;– Determine the ending balanceSELECT Balance INTO endBalanceFROM DailyEndBalanceWHERE AccountID = inAccountID && PostingDate = inEnd;EXCEPTION WHEN NO_DATA_FOUND THENendBalance := 0;END;medianBalance := ( startBalance + endBalance ) / 2;IF medianBalance < 0 THENmedianBalance := 0;END IF;IF medianBalance >= 500 THENinterest := medianBalance * 0.01;END IF;RETURN interest;END; CREATE OR REPLACE FUNCTION CalculateAccountInterest( inAccountID IN NUMBER,inStart IN DATE,inEnd IN DATE )RETURN NUMBER;ASmedianBalance NUMBER;startBalance NUMBER;endBalance NUMBER;BEGINstartBalance := GetDailyEndBalance ( inAccountID, inStart );endBalance:= GetDailyEndBalance ( inAccountID, inEnd );medianBalance := CalculateMedianBalance ( startBalance, endBalance );RETURN CalculateInterest ( medianBalance );END;
Introduce Variable. Put the result of the expression, or parts of the expression, in a temporary variable with a name that explains the purpose.	Before After CREATE OR REPLACE FUNCTION DetermineAccountStatus( inAccountID IN NUMBER,inStart IN DATE,inEnd IN DATE )RETURN VARCHAR;ASlastAccessedDate DATE;BEGIN– Some code to calculate lastAccessDateIF ( inDate < lastAccessDate && outdate > lastAccessDate )&& ( inAccountID > 10000 )&& ( inAccountID != 123456 && inAcountID != 987654) THEN— do somethingEND IF;— do another thingEND; CREATE OR REPLACE FUNCTION DetermineAccountStatus( inAccountID IN NUMBER,inStart IN DATE,inEnd IN DATE )RETURN VARCHAR;ASlastAccessedDate DATE;isBetweenDates BOOLEAN;isValidAccountID BOOLEAN;isNotTestAccount BOOLEANBEGIN— Some code to calculate lastAccessDateisBetweenDates := inDate < lastAccessDate && outdate > lastAccessDate;isValidAccountID := inAccountID > 100000;isNotTestAccount := inAccountID != 123456 && inAcountID != 987654;IF isBetweenDates && isValidAccountID && isNotTestAccount THEN— do somethingEND IF;— do another thingEND;
Parameterize Methods. Create one method that uses a parameter for the different values.
Remove Control Flag. Use remove or break instead of a variable acting as a control flag.	Before After DECLAREcontrolFlag := 0;anotherVariable := 0;BEGINWHILE controlFlag = 0 LOOP— Do somethingIF anotherVariable > 20 THENcontrolFlag = 1;ELSE— Do something elseEND IF;END LOOP;END; DECLAREanotherVariable := 0;BEGINWHILE anotherVariable <= 20 LOOP— Do something— Do something elseEND LOOP;END;
Remove Middleman. Get the caller to call the method directly.	Before After CREATE OR REPLACE PROCEDURE AProcedureparameter1 IN NUMBER;“¦parameterN IN VARCHAR;ASBEGINEXECUTE AnotherProcedure ( parameter1, “¦, parameterN );END;
Remove Parameter. Remove a parameter no longer used by the method body.
Rename Method. Rename an existing method with a name that explains the purpose.
Reorder Parameters. Change the order of the parameters of a method.
Replace Literal With Table Lookup. Replace Code constants with values from database tables.	Before After CREATE OR REPLACE FUNCTION CalculateInterest( inBalance IN NUMBER )RETURN NUMBER;ASinterest := 0;BEGINIF inBalance >= 500 THENinterest := medianBalance * 0.01;END IF;RETURN interest;END; CREATE OR REPLACE FUNCTION CalculateInterest( inBalance IN NUMBER )RETURN NUMBER;ASinterest := 0;minimumBalance NUMBER;interestRate NUMBER;BEGINminimumBalance := GetMinimumBalance();interestRate := GetInterestRate();IF inBalance >= minimumBalance THENinterest := medianBalance * interestRate;END IF;RETURN interest;END;
Replace Nested Expression With Guard Clauses. Remove nested if conditions with a series of separate IF statements.	Before After BEGINIF condition1 THEN— do something 1ELSEIF condition2 THEN— do something 2ELSEIF condition3 THEN— do something 3END IF;END IF;END IF;END; BEGINIF condition1 THEN— do something 1RETURN;END IF;IF condition2 THEN— do something 2RETURN;END IF;IF condition3 THEN— do something 3RETURN;END IF;END;
Replace Parameter With Specific Methods. Create a separate method for each value of the parameter.
Split Temporary Variable. Make a separate temporary variable for each assignment.	Before After DECLAREaTemporaryVariable := 0;farenheitTemperature := 0;lengthInInches := 0;BEGIN— retrieve farenheitTemperatureaTemporaryVariable := (farenheitTemperature – 32 ) * 5 / 9;— do something— retrieve lengthInInchesaTemporaryVariable := lengthInInches * 2.54;— do somethingEND; DECLAREcelciusTemperature := 0;farenheitTemperature := 0;lengthInCentimeters := 0;lengthInInches := 0;BEGIN— retrieve farenheitTemperaturecelciusTemperature := (farenheitTemperature – 32 ) * 5 / 9;— do something— retrieve lengthInIncheslengthInCentimeters := lengthInInches * 2.54;— do somethingEND;
Substitute Algorithm. Replace the body of the method with the new algorithm.

Source

This book describes, in detail, how to refactor a database schema to improve its design. The first section of the book overviews the fundamentals evolutionary database techniques in general and of database refactoring in detail. More importantly it presents strategies for implementing and deploying database refactorings, in the context of both “simple” single application databases and in “complex” multi-application databases. The second section, the majority of the book, is a database refactoring reference catalog. It describes over 60 database refactorings, presenting data models overviewing each refactoring and the code to implement it.

Catalog of Database Refactorings: Method Refactorings (original) (raw)

Source

Recommended Reading