SQLFlow: PLSQL MultiDiagrammatic Source Code Visualization

1
SQLFlow PL/SQL Multi-Diagrammatic Source Code
Visualization

• Samir Tartir Ayman Issa
• startir_at_uga.edu Ayman2.Issa_at_uwe.ac.uk

2
Outline

• Introduction
• SQLFlow Overview
• Architecture
• Components
• Example
• Feature Comparison
• Conclusions and future work
• Questions

3
Why documentation?

• Documenting source code is good. It increases
• Maintainability
• Reusability
• Interoperability
• Training
• Most computer systems include a database at the
  back-end.
• Usually documentation is prepared for the
  front-end code (Java, .Net, ), but less
  attention is paid for documenting the database.

4
SQL Flow

• Produces diagrams from plain PL/SQL code.
• Produces two types of diagrams
• Flowchart (FC)a diagrammatic representation of
  a process by representing processes and decisions
  of the process by geometric shapes (e.g. boxes,
  diamonds)
• Flow graph (FG)depicts a logical control flow,
  a node represents a sequence of non-branching
  statements
• Produces code metrics
• FC Max nesting depth, number of SQL, If, and
  loop statements.
• FG cyclomatic complexity, number of regions and
  predicates

5
SQLFlow Architecture

• Three tiers
• GUI Builder (View) analyzes the results of
  parsing the source code and generates the
  corresponding diagrams
• Parser (Controller) holds the parsing and
  analyzing business logic component.
• File management (persistence model) holds
  routines for storing diagrams.

6
Component 1 CODE PARSER

• Finds the lexical tokens
• Identify the tokens that will change the flow of
  control.
• Three types of statements
• Sequential
• Conditions
• Repetition

7
Component 1 CODE PARSER Algorithm

• For each line in the input file
• Read current word
• Switch Word
• Case "--" single-line ignore
• Case "/" multiple-line comment find
  closing and ignore
• Case "BEGIN" Signal a new program block
• Case "DECLARE" ignore and step to 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

8
Component 2 GUI BUILDER

• Generates the flowchart and flow graph diagrams.
• Listens to signals of the parser and dynamically
  construct diagrams as their components are being
  discovered.

9
Component 2 GUI BUILDER Algorithm

• "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.
• "LOOP" Draw a parallelogram with the word
  "Loop".
• "FOR" Draw a parallelogram with the loop
  condition.
• "WHILE" Draw a parallelogram with the loop
  condition.
• "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.
• "Assignment" Draw a rectangle with the
  assignment statement.
• "CALL" Draw a rectangle with the call.

10
Example Flowchart

• declare
• a number
• b number
• c number
• d number
• e number
• f number
• g number
• h number
• i number
• j number
• begin
• Proc1Proc2(a, b, c)
• raise form_trigger_failure
• if a1 or(b2 and(c3 and(d4 or(e5
• and f6)or g7)and h8)or(i9 and
• j10)) then
• message('Condition met')
• else

11
Example Flow Graph
declare a number b number c number d
number e number f number g number h
number i number j number begin
Proc1Proc2(a, b, c) raise form_trigger_failure
if a1 or(b2 and(c3 and(d4 or(e5 and
f6)or g7)and h8)or(i9 and j10)) then
message('Condition met') else
message('Condition Failed') end if FOR K IN
1..54 LOOP MESSAGE(I) END LOOP end
12
Feature Comparison

• SQLFlow was compared against two
  commercially-available products
• Code visual to flowchart
• Visutin

13
Conclusion and future work

• SQLFlow facilitates knowledge sharing and eases
  the burden of understanding and maintaining the
  existing systems.
• SQLFlow dominates over current commercial
  products by its powerful multi-diagrammatic and
  metrics extraction features.
• Future work
• Visualize the source code using the standard UML
  activity diagrams.
• Integrate SQLFlow into a UML design tool, making
  the PL/SQL code a part of the formal model of the
  application.
• Metrics of a multitude of runs of SQLFlow can be
  used in cost estimation and planning of
  prospective software development projects.

14
Questions