Transcript Wipro Presentation Template

Grammar Test
Framework
DBMS-3
Leader : Harish Narayana / Minho Hwang
Member : Sook-youn Kwon, Jong-hyeok Lim
Yu-jin Bae, Jeong-kyeom Kim
Man-jae Ha, Khushboo, Jayesh
2009. 11. 27
© 2008 Wipro Ltd - Confidential
1
Agenda
Introduction
SQL Grammar (BNF)
Project Scope
Demo Step
Limitation and Future work
© 2008 Wipro Ltd - Confidential
2
Introduction
BNF
File
Log
File
Introduction :
- The Grammar Test Framework is the solution to assure Quality for the
database vendors.
- You can test your DBMS with various other DBMS(s) for consistency in results.
- To test DBMS for their ANSI SQL 2003 compliance.
© 2008 Wipro Ltd - Confidential
SQL Grammar (BNF)
BNF Grammar for ISO/IEC 9075-2:2003
<query specification> ::= SELECT [<set quantifier>] <select list> <table expression>
<set quantifier>
::= DISTINCT | ALL
<select list>
::= <asterisk> | <select sublist>
<select sublist>
::= <derived column>
<derived column>
::= <value expression>
<value expression>
::= <column reference> | <set function specification>
<column reference> ::= <column name>
<set function specification> ::= <general set function>
<general set function> ::= <set function type> <left paren> <function column>
<right paren>
<set function type>
::= AVG | MAX | MIN | SUM | COUNT
<table expression>
::=
<from clause>
[ <where clause> ]
[ <group by clause> ]
[ <having clause> ]
[ <window clause> ]
….. and so on
© 2008 Wipro Ltd - Confidential
Project Scope
Select Query
- Generating Select Query
No
Clause
1
Simple query
SELECT columns FROM tables
2
WHERE
WHERE columns op condition
3
GROUP BY
4
HAVING
HAVING columns
5
Aggregate Function
function (columns)
6
ORDER BY
7
JOIN expression
8
Sub-Query
(1 level)
Description
GROUP BY columns
ORDER BY columns sort type
table join type table ON condition
FROM (sub-query) alias
WHERE column op (sub-query)
Having column op (sub-query)
© 2008 Wipro Ltd - Confidential
Demo Step – 1.Select SQL BNF file
© 2008 Wipro Ltd - Confidential
Demo Step – 2.Add DBMS to test
© 2008 Wipro Ltd - Confidential
Demo Step – 2.Add DBMS to test
Main Frame (GUI)
UI Controller
Display
Controller
Server
Controller
Controller
Monitoring
Generator
(Random Query)
Test
DB
Setter
View
Analyzer
Testing
Controller
Model
Grammar
Domain
Parser
Logger
Model-view-control
(MVC) is Schema
an architectural
DBMS
Grammar
pattern usedManager
in software engineering.
Builder
Reader
The pattern isolates business logic from input and
presentation. Permitting independent development,
testing
Embeddedand maintenance of each.
Server
Setting DB
DBMS1
DBMS2
DBMS3
Generate© Query
2008 Wipro Ltd
[ MVC model ]
View
Controller
Model
Fire Query
- Confidential
Analyze result
Demo Step – 3.Start testing
© 2008 Wipro Ltd - Confidential
Demo Step – 3.Start testing
Main Frame (GUI)
UI Controller
Display
Controller
Server
Controller
Testing
Controller
SQL QUERY
Monitoring
Generator
(Random Query)
Test
DB
Setter
Analyzer
Domain
Grammar
Parser
Logger
DBMS
Manager
Schema
Builder
Grammar
Reader
[ MVC model ]
View
Controller
Embedded
Server
Setting DB
DBMS1
DBMS2
DBMS3
Generate© Query
2008 Wipro Ltd
Model
Fire Query
- Confidential
Analyze result
Demo Step – 3.Start testing
Main Frame (GUI)
UI Controller
Display
Controller
Server
Controller
Monitoring
Testing
Controller
Generator
(Random Query)
Test
DB
Setter
Analyzer
Domain
Grammar
Parser
Logger
DBMS
Manager
Schema
Builder
Grammar
Reader
[ MVC model ]
View
Controller
Embedded
Server
Setting DB
DBMS1
DBMS2
DBMS3
Generate© Query
2008 Wipro Ltd
Model
Fire Query
- Confidential
Analyze result
Demo Step – 4.Anlyze Result of Query
Main Frame (GUI)
UI Controller
Display
Controller
Server
Controller
Monitoring
Testing
Controller
Generator
(Random Query)
Test
DB
Setter
Analyzer
Domain
Grammar
Parser
Logger
DBMS
Manager
Schema
Builder
Grammar
Reader
[ MVC model ]
View
Controller
Embedded
Server
Setting DB
DBMS1
DBMS2
DBMS3
Generate© Query
2008 Wipro Ltd
Model
Fire Query
- Confidential
Analyze result
Demo Step – 5.View Testing Log
© 2008 Wipro Ltd - Confidential
Demo Step – 5.View Testing Log
© 2008 Wipro Ltd - Confidential
Limitation and Future work
1
Limitation

Presently only 77 production rules in the SQL BNF grammar file

Uses predefined Logical Data Model information

Difficulty in implementing classes for extendibility of SQL BNF grammar
2
Future work

Support for various SQL Query. (DML, DDL and DCL)

Generate query of all possible combination.

Extendibility to apply SQL Grammar file from DBMS vendors.

Use dynamic logical data model information define by user
© 2008 Wipro Ltd - Confidential
Thank You
Min-ho Hwang
Project Leader
[email protected]
November11, 2009
© 2008 Wipro Ltd - Confidential
16