Informatika | Adatbázisok » Tartir-Issa - SQLFlow, PL/SQL Multi-Diagrammatic Source Code Visualization

SQLFlow: PL/SQL Multi-Diagrammatic Source Code Visualization Samir Tartir Department of Computer Science University of Georgia Athens, Georgia 30602 USA Email: startir@uga.edu Ayman Issa University of the West of England (UWE), Coldharbour Lane, Frenchay, Bristol BS16 1QY United Kingdom Email: Ayman2.Issa@uweacuk introducing new problems to other code that was perfectly working before. Therefore, educating programmers about the behaviors of the target code before start modifying it is not an easy task. Source code visualization is one of the heavily used techniques in the literature to overcome this lack of documentation problem, and its consequent knowledge-sharing problem [7, 9]. ABSTRACT: A major problem in software maintenance is the lack of a well-documented source code in software applications. This has led to serious difficulties in software maintenance and evolution. In particular, for those developers who are faced with the task of fixing or modifying a piece of code they

never even knew existed before. Database triggers and procedures are parts of almost every application, are typical examples of badly documented software components being the hidden back end of software applications. Source code visualization is one of the heavily used techniques in the literature so as to familiarize software developers with new source code, and compensate for the knowledge-sharing problem. Therefore, a new PL/SQL flowcharting reverse engineering tool, SQLFlow, has been designed and developed. SQLFlow is a three tiers architecture tool that dominates over the currently available flowcharting tools by its powerful multi-diagrammatic and source code metrics extraction capabilities. Finally, future work is planned to integrate SQLFlow with the UML modelling standards in the software industry. Therefore, this research aims at investigating the current database source code visualization tools and identifying the main open issues for research. Consequently, the mostly used

PL/SQL database programming language has been selected for a flowcharting reverse engineering prototype tool called "SQLFlow". SQLFlow has been designed, implemented, and tested to parse the target PL/SQL code stored in database procedures, analyze its structure, and render its flow in a visual flowchart. Section 2 presents a brief summary for the most wellknown flowcharting tools. Section 3 discusses the high level architecture and the underlying components of SQLFlow. Section 4 contrasts the features of SQLFlow against the available literature. The validation and verification of SQLFlow is detailed in section 5. Finally, an evaluation including an outline of future research work is presented in section 6. KEYWORDS: PL/SQL, Flowchart, Reverse engineering, legacy systems. 1. INTRODUCTION 2. COMMERCIAL FLOWCHARTING TOOLS Databases (DBs) form a part of almost every computer application that is currently in use [11]. A huge effort is usually put in designing and building a

database and its underlying stored packages, procedures, and triggers. As a part of a larger system, databases usually are called the "back end" of the system, and this has usually led to not paying enough attention to document code that is stored within, contrary to code of a the "front end" which is usually better documented [10]. On the professional level, code visual to flowchart and Visutin [1, 3] are the most well known and commonly used commercial flowcharting tools in the literature. Both tools are so powerful to support source code visualization of several programming languages such as: C, C++, C#, VB, Java, and much more. But, they also share the same limitation of supporting very small number of DB scripting languages compared to the supported number of high level programming languages. In many cases, there is no documentation of the project source code and the people who originally wrote the code no longer work with the company. This situation might

cause problems for programmers who are faced with the task of fixing or modifying a database code they never even knew existed before. The underlying operational principle of both tools is identical. They rely on the system user to define the physical location of the target source code to analyze and visualize. However, they differ in the way they present their output and how they integrate with other external systems such as: VISIO and MS Word [1, 3]. In that, Visutin seems to show more powerful integration with external systems. The results of such a situation may be the introduction of more problems than the ones that were intended to fix, or 1 Figure 1: SQLFlow Architecture. The main responsibility of the parser is to find the lexical Unfortunately, both tools concentrate on flowcharting tokens of the source code and identify the tokens that will program visualization and ignore the other useful change the flow of control. Therefore, it goes through the diagrams that might

be of interest to the users in the input PL/SQL source code file, line by line, and identifies different stages of the software development life cycle the keywords that will determine its execution flow. For (SDLC). Moreover, they also discard the role of several example, it will recognize a "Begin" keyword, as a start of complexity metrics that could be extracted from the a new bock. On the other hand, finding an "If" statement source code, which are highly beneficial to the different will start a branching statement that should be closed by stakeholders in the different stages of the SDLC. an "End If" or "Else" keyword. On the other hand, several academic attempts [7] have Currently, there are three types of control flow been cited in the literature as prototyping tools to support statements; sequential, conditions and repetition. While more types of diagrams, but not metrics. Kita et al [7] is a accessing each token, the parser dynamically

involves the typical example for a prototype tool that that aims at GUI builder component that will handle any requirements educating Java novice programmers by generating flow to reflect the tokens action on the chart. Figure 2 presents charts and paths for their target Java files. the pseudo code of the parser’s component workflow. This raises the flag for an urgent need for a new source For each line in the input file code visualization tool that overcome the above major Read current word; drawbacks of the most well known flowcharting tools. Case Word Case "--": single-line: ignore; Correspondingly, SQLFlow has been designed to not only Case "/*": multiple-line comment: generate the flow chart of the target Oracle PL/SQL find closing and ignore; source code, but also the Flow graph. In addition, several Case "BEGIN": Signal a new program metrics are now extracted from the source code to block; Case "DECLARE": ignore and step to facilitate

the subsequent analysis and testing phases. the next "Begin"; Case "EXCEPTION": Signal an exception block; Case "ELSE": Signal else; Case "IF": Find the corresponding "THEN" and signal "IF" block; Case "LOOP": Signal "LOOP" block; Case "FOR": Find the corresponding "LOOP" statement and signal a "FOR" block; Case "WHILE": Find the corresponding "LOOP" statement and signal a "WHILE" block; Case "END": Get next word Case ";": Close last "BEGIN" block; Case "LOOP": Close last "LOOP, FOR, or WHILE" block; Case "IF": Close last "IF" block; Case "WHEN": Find the corresponding "THEN" and Signal a "WHEN" block; Case "RAISE": find the corresponding ";" and signal a statement; Otherwise: "one-word;", "SQL",

"oneword.;", or :=;": Get next word: Case SQL: SQL Statement: find corresponding ";" and signal statement; Case Assignment: Assignment statement: Signal assignment statement; Otherwise: Procedure call: Signal procedure call statement; 3. SYSTEM ARCHITECTURE AND COMPONENTS SQLFlow is a light weight MS Visual Basic 6.0 tool designed to read an input file stored from an Oracle PL/SQL database procedure and generate the corresponding flowchart and flow graph that reflect its operational flow. A model-view-controller (MVC) three-tier architecture [10] has been adopted in SQLFlow implementation to separate the concerns of the different system components. The first tier, the view tier, is represented by a graphical user interface (GUI) Builder component that is responsible on analyzing the results of parsing the source code and generating the corresponding diagrams. The middle tier, the controller tier, holds the parsing and analyzing business logic component. And,

the last tier, the persistence model tier, is represented by the file management component. Figure 1 depicts the visual view of the system architecture. Sections 31 and 32 discuss the details of the controller and view components, respectively. 3.1 SOURCE CODE PARSING COMPONENT Figure 2: Parser Component Pseudo Code. 2 maintenance phases; while future projects could combine the extracted metrics with effort and productivity information to be reused in estimating new software development projects. 3.2 GUI BUILDER COMPONENT The main responsibility of the GUI builder component is to handle the actual generation of the flowchart and flow graph diagrams. It "listens" to signals by the parser, and processes each signal appropriately to draw the correct shapes to reflect the actions of the signal. For example, if the signal was for a loop, the drawer will draw the loop parallelogram shape and will keep a note of an open loop that needs an "end loop;" statement to

close it. Figure 3 summarizes the parser signals caught by the GUI builder and its corresponding reaction. "BEGIN": Draw a rectangle with the word "Begin". "EXCEPTION": Draw a rectangle with the word "Exception". "IF": Draw a diamond containing the condition. "ELSE": Draw another branch for the last "IF" diamond and move drawing to that branch. Hence, the GUI Builder builds the required diagrams dynamically by showing each shape that is being added to the diagram. This makes it easier to follow the progress of the parsing process and trace the generated diagrams to its source code fragment. "LOOP": Draw a parallelogram with the word "Loop". "FOR": Draw a parallelogram with the loop condition. 4. SQLFLOW VALIDATION AND EVALUATION "WHILE": Draw a parallelogram with the loop condition. Several DB applications have been targeted to validate and verify SQLFlow. An iterative

validation and verification process has been adopted to evaluate the generated flowcharts, flow graphs, and source code metrics. The intention is to prioritize SQLFlow functionalities and get the core functionality, source code parsing and analyzing, delivered earlier. Then, the other supportive functionalities, GUI and file management, are delivered in subsequent iterations. Further, focusing on validating and verifying one component at a time had resulted in cleaner and more structured source code to facilitate future reusing and integration with other systems. On the other hand, earlier iterations functionalities tend to receive more testing with every new system version [5]. "END": Draw a rectangle that closes the last "BEGIN" and draw a line between the "BEGIN" and "END" rectangles. "END LOOP": Draw a rectangle that closes the last "LOOP" and draw a line between the "LOOP" parallelogram and the "END"

rectangle. "END IF": Draw a rectangle that closes the last "IF" and draw a line between the "IF" diamond and the "END" rectangle. "WHEN": Draw a diamond containing the condition. "RAISE": Draw a rectangle with the raise statement. "SQL": Draw a rectangle with the SQL statement. Figures 4, 5, and 6 present a sample PL/SQL source code, and its corresponding flowchart, and flow graph, respectively, generated using SQLFlow. The top right corner of the flowchart and flow graph diagrams is allocated for the resulting source code metrics. The flowchart related metrics [4] are: maximum nesting depth, total number of SQL statements, total number of conditional statements, and total number of repetition statements. On the other hand, the flow graph metrics are: cyclomatic complexity [8], number of regions, and number of predicates. 5. SQLFLOW FEATURES COMPARISON A typical SDLC consists of 5 main phases: requirements

analysis, design, implementation, testing, and deployment. The objective of the extracted source code metrics is to be used as a historical data to guide and inform current and future software development projects. Current projects could utilize the extracted metrics, e.g cyclomatic complexity, conditional statements, etc, to inform the upcoming testing, deployment, and Comparing SQLFlow features to the available commercial and academic flowcharting tools [1, 3, 7]; it has been found that SQLFlow dominates over them from different perspectives. In particular, the multidiagrammatic and metrics extraction appear to be unique in SQLFlow. However, further development is needed to extend SQLFlow scalability to consider large volumes DB triggers and procedures. Table 1 summarizes the "Assignment": Draw a rectangle with the assignment statement. "CALL": Draw a rectangle with the call. Figure 3: GUI Builder Caught Signals and itsCorresponding Reaction. 3 results of

contrasting the different flowcharting tools with SQLFlow. declare a number; b number; c number; d number; e number; f number; g number; h number; i number; j number; begin Proc1;Proc2(a, b, c); raise form trigger failure; if a=1 or(b=2 and(c=3 and(d=4 or(e=5 and f=6)or g=7)and h=8)or(i=9 and j=10)) then message(Condition met); else message(Condition Failed); end if; Figure 6: Sample PL/SQL Source Code Flow Graph. Table 1: Flowcharting Tools Features Comparison. FOR K IN 1.54 LOOP MESSAGE(I); END LOOP; end; Feature Support Function and Procedure Call Provide Source Code Metrics Support Error Detection Operating System Limitations Performance Generated Diagrams Clarity Support Multiple Diagrams Generation Figure 4: Sample PL/SQL Source Code. Code visual to flowchart Visutin SQLFlow No Yes Yes No No Yes No No Yes No Yes Yes High High High High High High No No Yes 6. CONCLUSION AND FUTURE WORK A new PL/SQL flowcharting tool, SQLFlow, has been design, built,

and presented. It aims at facilitating the process of knowledge sharing between the different software development team members. On the other hand, it eases the burden of understanding and maintaining the existing legacy systems. SQLFlow is a light weight three-tier architecture tool that separates the concerns of the embodied diverse components. Contrasting SQLFlow against other professional and academic flowcharting tools, it has been concluded that SQLFlow dominates over them by its powerful multi-diagrammatic and metrics extraction features. However, further development is needed to extend SQLFlow scalability. Figure 5: PL/SQL Source Code Sample Flowchart. 4 [4] Fenton, N. and Neil, M, (2000) Software Metrics:Roadmap in International Conference on Software Engineering New York, NY, USA ACM Press, pp.357-370 Future work is planned to upgrade SQLFlow to visualize the source code using the standard UML [2] activity diagrams. This paves the way to integrate SQLFlow into a UML

design tool, such as rational rose, making the PL/SQL code a part of the formal model of the application. [5] Harrold, M., (2000) Testing:a Roadmap in International Conference on Software Engineering New York, NY, USA IEEE-CS: Computer Society, pp.61-72 Finally, applying SQLFlow to several legacy DB systems will result in building large volume multi-purpose software metrics repository that could be utilized to inform the diverse SDLC phases [6], e.g cost estimation and planning phase, of prospective software development projects. [6] Issa, A., Odeh, M, and Coward, D, (2005). Using Use Case Models to Generate Object Points. in Kokol, P. Proceedings of the IASTED International Conference on Software Engineering Austria. ACTA Press, pp468-473 [7] Kita, Y. Kawasoe T Katayama T, (2005) Prototype Of An Automatic Visualization Tool For Java To Educate Novice Programmers in Kokol, P. Proceedings of the IASTED International Conference on Software Engineering Austria. ACTA Press, pp.307-312

7. ACKNOWLEDGEMENT Samir Tartir would like to express his gratitude to Prof. Talib Al-Sari for his useful feedback during SQLFlow development. REFERENCES [8] McCABE, T., (1976) A Complexity Measure IEEE Transactions on Software Engineering, 2 (4), pp. 308-320 [1] Aivosto, (2003). Visustin Flow Chart Generator [online]. Aivosto.com Available from: http://www.aivostocom/visustinhtml [Accessed 21-5-2005]. [9] Quatrani, T., (2000) Visual Modeling With Rational Rose 2000 and UML. Rev ed Boston, Mass. London: Addison Wesley [2] Booch, G., Rumbaugh, J and Jacobson, I, (1999) The Unified Modeling Language User Guide. Reading, Mass. Harlow: Addison-Wesley [10] Sommerville, I., (2001) Software Engineering 6th ed. Harlow, England ; New York : AddisonWesley [3] Fatesoft (1997). Code Visual to Flowchart [online] USA: Available from: http://www.fatesoftcom/s2f/ [Accessed 21-52005] [11] Sommerville, Ian and Kotonya, Gerald, (1998). Requirements Engineering: Processes and Techniques.

Chichester, New York : J Wiley 5