Build a SQL-subset compiler program IN JAVA that has semantic checks and syntax

Question

Build a SQL-subset compiler program IN JAVA that has semantic checks and syntax checks. You will build an abstract syntax tree and perform the following semantic checks:

- Type checking (in the where-clause).

- Valid references to database objects (e.g. column not defined, relation does not exist in catalog etc.)

- Is nested query "co-related" or not?

The program should be interactive that behaves as follows (These are your test cases as well.) Take note that the program ends as soon as a ";" is inputed (Do not submit an answer that is not coded in Java):

--------------------------------------------------------------------------------------------------------------------

(Test Case 1)

$ java OurSQL

SQL>select pno,pname

SQL>from parts;

No Semantic Errors.

Not Nested.

--------------------------------------------------------------------------------------------------------------------

(Test Case 2)

$ java OurSQL

SQL>select distinct CNAME

SQL>from CUSTOMERS

SQL>where CNO in (select CNO

SQL>from ORDERS

SQL>where ONO in (select ONO

SQL>from ODETAILS

SQL>where PNO in (select PNO

SQL>from PARTS

SQL>where PRICE < 20.00 )));

No Syntax Error

No Semantic Error

Nested-Not Correlated

--------------------------------------------------------------------------------------------------------------------

(Test Case 3)

$ java OurSQL

SQL>select CNO

SQL>from ORDERS

SQL>where exists (select ONO

SQL>from ODETAILS

SQL>where ODETAILS.ONO = ORDERS.ONO and

SQL>ODETAILS.PNO = 10506) and

SQL>exists (select ONO

SQL>from ODETAILS

SQL>where ODETAILS.ONO = ORDERS.ONO and

SQL>ODETAILS.PNO = 10507) and;

Syntax Error

--------------------------------------------------------------------------------------------------------------------

(Test Case 4)

$ java OurSQL

SQL>@q1;

SQL>select CNO

SQL>from ORDERS

SQL>where exists (select ONO

SQL>from ODETAILS

SQL>where ODETAILS.ONO = ORDERS.ONO and

SQL>ODETAILS.PNO = 10506) and

SQL>exists (select ONO

SQL>from ODETAILS

SQL>where ODETAILS.ONO = ORDERS.ONO and

SQL>ODETAILS.PNO = 10507);

No Syntax Error

No Semantic Error

Nested-Correlated

Explanation / Answer

trait SQL extends AST
{
import scala.util.parsing.combinator._

object Grammar extends JavaTokenParsers with PackratParsers {

def stm: Parser[Operator] =
selectClause ~ fromClause ~ whereClause ~ groupClause ^^ {
case p ~ s ~ f ~ g => g(p(f(s))) }

def whereClause: Parser[Operator=>Operator] =
opt("where" ~> predicate ^^ { p => Filter(p, _:Operator) }) ^^ { _.getOrElse(op => op)}
def groupClause: Parser[Operator=>Operator] =
opt("group" ~> "by" ~> fieldIdList ~ ("sum" ~> fieldIdList) ^^ {
case p1 ~ p2 => Group(p1,p2, _:Operator) }) ^^ { _.getOrElse(op => op)}

def joinClause: Parser[Operator] =
("nestedloops" ~> repsep(tableClause, "join") ^^ { _.reduceLeft((a,b) => Join(a,b)) }) |
(repsep(tableClause, "join") ^^ { _.reduceLeft((a,b) => HashJoin(a,b)) })
def tableClause: Parser[Operator] =
tableIdent ~ opt("schema" ~> fieldIdList) ~
opt("delim" ~> (""" """ ^^ (_ => ' ') | """.""".r ^^ (_.head))) ^^ {
case table ~ schema ~ delim => Scan(table, schema, delim) } |
("(" ~> stm <~ ")")

def predicate: Parser[Predicate] =
ref ~ "=" ~ ref ^^ { case a ~ _ ~ b => Eq(a,b) }
def ref: Parser[Ref] =
fieldIdent ^^ Field |
"""'[^']*'""".r ^^ (s => Value(s.drop(1).dropRight(1))) |
"""[0-9]+""".r ^^ (s => Value(s.toInt))
  
def parseAll(input: String): Operator = parseAll(stm,input) match {
case Success(res,_) => res
case res => throw new Exception(res.toString)
}
}
}

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.