PL/SQL Code Restructure With Structural Resemblance Measure

Abstract

Database design is a challenging and complex task due to its importance in the overall performance of the database system. Recently, various techniques have been under use across different stages of database application modelling, design and implementation and they have been under continuous study and improvements by various researchers. However, the existing techniques are not providing effective guidelines for optimal database design. Hence, this research work proposes new improvements to the existing design techniques in specific phase, namely programming in the database environment. In this work, we have suggested a method for restructuring PL/SQL code.

Keywords: Refactoring, Database, PL/SQL, gaming theory

1. Introduction

Code smells or bad smells are signs of potential problems in the source code of a program code. The concept of code smell was introduced as an indicator of problems within the design of software. Detection of code smells have become an established method to indicate software design issues that may cause problems for further development and maintenance [7], [8]. Consequently, the consensus is that code with smells should be refactored to prevent or reduce such problems [9]. A major challenge in the modern information society is ensuring the maintainability of increasingly large and complex software systems[10]. Code fragments may appear in more than one location. Long methods are generally hard to read and comprehend. Large classes with much functionality are too complex and confusing. Methods with too many arguments or return objects are difficult to manipulate. Lazy classes are not doing useful work. Data classes have data fields and access methods only but do not have behavior. A method or a fragment of a method may be more interested in features of another class than those of the enclosing class. All of the above situations can be regarded as bad smells in code.

Restructuring PL/SQL is necessary to perk up code design. Like other software artifacts, very often code undergoes changes throughout its lifecycle. During the software development and evolution, since changes are requested, novel features are added to the system. The internal structure of the code changes as the system experiences modification which makes the source code to drift away from its original design. Refactoring is the solution to the process of restructuring the code that drifts away from the software quality so as to perk up the software quality aspects. It is a non-trivial task to recognize where the refactoring should be applied but when it has to be applied is based on human intuition.

1.1 Restructuring Issues

First the appropriate level of abstraction that can be applied for refactoring has to be determined. Then, decision has to be made on whether the refactoring has to be applied to the source code or to a more abstract software artifact such as design model or requirement document. Refactoring is done normally for code. For code refactoring the most widespread approach to detect program parts that require refactoring is the identification of bad smells.

Get Help With Your Essay

If you need assistance with writing your essay, our professional essay writing service is here to help!
Find out more about our Essay Writing Service

The existence of bad smells in code like long method, feature envy, move method, extract class, large class, and lazy class indicate the necessity for applying refactoring. Identifying when to apply refactoring and where to apply it are the key problems in restructuring PL/SQL code.

Refactoring can be classified based on which quality attributes they affect. This permits us to perk up the worth of software by applying the relevant refactoring at the right place. To accomplish this, the purpose of each refactoring and its effect has to be analyzed. Based on the refactoring techniques used, some remove code redundancy, some raise the level of abstraction and others boost the reusability.

2. Literature Review

Fokaefs et al [1] proposed a methodology which identifies extract class refactoring opportunities by a class decomposition method. An agglomerative clustering algorithm is used based on the Jaccard distance between class members. In terms of cohesion this work facilitates to identify new concepts and rank the solutions according to their impact on the design quality of the system. Specific kind of bad smells called “God Class” is considered for this purpose. Data god class and behavioral god class are defined. A class which has many system’s data in terms of number of attributes is called data god class. When it has greater portion of the systems functionality in terms of number and complexity of methods is called behavioral god class. Behavioral god class may be avoided by splitting the class by extracting a cohesive and independent piece of functionality. This refactoring is called “Extract Class”. Two projects namely eRisk, an electronic adaptation of the well known board game and SelfPlanner[2], an intelligent web based calendar application have been taken for this purpose and the result shows that this methodology identifies relatively large number of new concepts that can be potentially extracted in new classes.

Tsantalis and Chatzigeorgiou et al [3] have proposed the placement of attributes or methods within classes in an object-oriented system which is usually guided by conceptual criteria and aided by appropriate metrics. Moving state and behaviour between classes can help reduce coupling and increase cohesion, but it is nontrivial to identify. They proposed a methodology for the identification of move method refactoring opportunities that constitute a way for solving many common feature envy bad smells.

Bavota et al [4] have proposed an approach using game theory to identify extract-class refactoring opportunities. Game theory techniques can often be used to deal contrasting goals. Game theory is successfully used to propose solutions to strategic situations, in which an individual’s success in making choices depends on the choices of others. It is very common in software engineering to find a solution often for problems having competing goals, like integrity versus efficiency, reusability versus reliability, reusability versus integrity, quality versus cost, cohesion versus coupling by the developers and managers. The extract-class refactoring problem can be modelled as a non-cooperative game involving two players. Given a class to be refactored, the two players contend with the methods of the original class to build two new classes with higher cohesion and lower coupling than the original class. Their approach considers S and T as two players in charge of building a new class selecting methods from the original class to be refactored. The process starts by assigning to S and T two methods that have the lowest structural and semantic similarity. S and T will then iteratively contend with the remaining methods of the class to be refactored. In single iteration, S or T could add at least one method to its class by considering the impact of adding the method on the cohesion and coupling of its class. The process stops when each method of the original class is assigned to either S or T. The move to be performed during iteration is chosen by finding the Nash equilibrium in the payoff matrix.

3. PROPOSED SYSTEM

Restructuring of PL/SQL code is guided by the structural similarity between methods which measures the relationship between procedures accessing the same relations. The PL/SQL code is restructured by grouping the procedures that access the same relations into one package by applying the game theory.

The extract-class refactoring trouble can be modelled as a non-cooperative game involving two players. Given a package to be refactored, the two players contend for the procedures of the original package to build two new packages with higher cohesion and lower coupling than the original packages.

The construction of the two packages is iterative once identified the move to be performed, each player adds to its package the procedure (if any) obtained performing the selected move, i.e., the player incrementally builds its package. The process stops when each procedure of the original package is assigned to one of the two players. The player can select utmost one procedure of the package to be refactored in all iterations. At the first iteration, the pair of procedures with lowest similarity is assigned to the two players. The two players will then iteratively contend for the remaining n-2 procedures to be refactored. So, in particular, during an iteration of the process, a player can perform one of the following moves:

i, j move: In the first move, the player A selects the procedure pi and yield the procedure pj to the opponent player B.

i, null move: In the second move, the player A selects the procedure pi while the opponent does not select any procedure.

null, j move: In the third move, the player A does not select any procedure while the opponent B selects the procedure pj.

The two indexes i and j are in [1 . . . p + 1] where p is the number of remaining procedure to be assigned. In the other way, the two indexes i and j are in [1 . . . n-1]. Apart from remaining procedure to be assigned (p), a null move must also be considered (p + 1). In our work i and j varies from 1 to 24. (No. of procedure is 25. One procedure is assigned to each player to start with. Therefore remaining n-2 is equal to 23. By adding one null move, index maximum value becomes 24.

The purpose of null move is to increase the wisdom of a player, i.e., a player is allowed to select a procedure only if there is a clear advantage in selecting it. Indeed, without the null move, a player must select a procedure at each iteration of the refactoring process.

In successive iteration, each player selects a procedure that is highly cohesive with the procedure it has selected in the previous iteration. If adding a procedure to the package build by a player decreases the cohesion of the package then the player on that iteration adds no procedure to the package and this action of the player is termed as null move.

The move to be performed during an iteration of the process is chosen by finding the Nash equilibrium in the payoff matrix. The Nash equilibrium are obtained using the algorithm defined by Mangasarian (1964) which enumerates all the equilibria in a two-player finite game. If there are more than one Nash equilibria, then the one having the highest sum of the payoffs of both players is selected. The process stops when each procedure of the package is assigned to any one of the two players.

The computation of Similarity Matrix and Pay-off Matrix are calculated using Similarity between Methods (SSM) mathematical model [5] and Nash mathematical model [6].

The payoff matrix is at the core of the approach. The generic entry of such matrix, p_i,j, is representing the payoffs of each player corresponding to a generic move (i, j). These payoffs depend on the impact of the procedure obtained by the two players on the cohesion and coupling of their procedure. In particular, pk measures the impact on cohesion obtained by player k selecting the method p_i balanced with the impact on coupling caused by yielding the procedure p_j to the other player.

4. EMPIRICAL EVALUATION

A PL/SQL code named first run in Pay Roll Management System developed using Oracle 10g is used for the study. The first run contains twenty five procedures.

The relations and attributes name accessed by twenty five procedures. The first run is not modularized on any aspect and so it is difficult to maintain. It is not easy to upgrade, customize with respect to changing requirements and debug. The concept of refactoring using game theory is applied to obtain packages that offer several advantages such as modularity, easier application design, information hiding, added functionality and better performance. In oracle PL/SQL code package, it is possible to have any procedures that may access different relations. By using this system, the first run is restructured by grouping the procedure that accesses the same relation into one package by applying game theory which leads to better maintainability and understandability.

It has been inferred that using the similarity by relations the number of packages obtained is 14 which is given in Table 1. The proposed approach for refactoring PL/SQL has restructured twenty five procedures into fourteen packages. This enables better maintenance and understanding.

Table 1. The number of packages obtained from similarity by relations

5. CONCLUSION

The proposed system achieves modularity by restructuring the code that accesses same data into single package thereby bringing out the advantage of encapsulation that is commonly achieved in object oriented programming into PL/SQL code by using packages. The modularization is done by applying game theory. The modularized code improves the maintainability and understandability of the system and so reduces the overhead whenever the real life scenario changes. Since the procedures accessing the same relations are grouped into a single package, any change that is done to the schema of the relation will be reflected only in one package and thereby reduces the ripple effect.

References

1. Marios Fokaefs, Nikolaos Tsantalis and Jorg Sander Alexander Chatzigeorgiou, “Decomposing Object-Oriented Class Modules using an Agglomerative Clustering Technique”, IEEE Conference, 2009.
2. Refanidis, I. and Alexiadis, A. “SelfPlanner: Planning your time”, ICAPS Workshop on Scheduling and Planning Applications, 2008.
3. Tsantalis, N. and Chatzigeorgiou, A.“Identification of Move Method Refactoring Opportunities”, IEEE Transactions on Software Engineering, Vol.35, No.3, pp.347-367, 2009.
4. Bavota, G., Oliveto, R. and Lucia, A.D. “Playing with refactoring: Identifying extract class opportunities through game theory”, in ICSM ‘10 Proc. of the IEEE International Conference on Software Maintenance, pp 1-5, 2010.
5. Gui, G. and Scott, P. D. “Coupling and Cohesion Measures for Evaluation of Component Reusability”, In: (MSR ‘06) Proceedings of the 2006 international workshop on Mining software repositories, 2006.
6. Nash, J. “Non-Cooperative Games”, Annals of Mathematics, Vol.54, No.2, pp.286-295, 1965.
7. Lanza, M and Marinescu, R. “Object-Oriented Metrics in Practice: Using Software Metrics to Characterize, Evaluate and Improve the Design of Object-Oriented Systems”. Springer 2006
8. Moha, N., Gueheneuc, Y.G. , Duchien, L. and Le Meur, A.-F. “Decor: A Method for the Specification and Detection of Code and Design Smells”, IEEE Transactions on Software Engineering, Vol.36, No.1, pp. 20-36, 2010.
9. Mens, T. and Tourwe, T. “A Survey of Software Refactoring”, IEEE Transactions on Software Engineering, Vol.30, No.2, pp.126-139, 2004.
10. Dag, I.K., Bente, A. And Audris. M, “Questioning Software Maintenance Metrics: A Comparative Case Study”, Proc. International Syposium on Empirical Software Engineering and Measurement, pp. 107-110, 2012.