package scala.dbc2.syntax import DataTypeSyntax._ import statement._ import statement.constraint._ import statement.expression._ import statement.AnyValueDef._ import value._ import scala.util.parsing.combinator.Parsers /** Define the body of the Parser for the SQL syntaxic grammar. Some things had to be changed from the * ISO 9075 (2003) SQL grammar to transform it into a LR grammar. */ class SQLParser extends PartialParser { import DBC2Tokens._ import TokenVal._ ///////////////////////////// ///////////REQUEST/////////// ///////////////////////////// private[syntax] lazy val request: Parser[Statement] = ( tableDefinition | insertStatement | dropTableStatement | deleteStatementSearched | querySpecification | updateStatementSearched ) ///////////////////////////// ///////////CREATE//////////// ///////////////////////////// private[syntax] lazy val tableDefinition: Parser[Create] = createAsQuery | createAsElementList private[syntax] lazy val createAsElementList: Parser[CreateAsElementList] = CREATE ~ opt(tableScope) ~ TABLE ~ tableName ~ LPAREN ~ rep1sep(tableElement, COMMA) ~ RPAREN ^^ { case _ ~ scope ~ _ ~ name ~ _ ~ elems ~ _ => CreateAsElementList(scope, name, elems) } private[syntax] lazy val createAsQuery: Parser[CreateAsQuery] = CREATE ~ opt(tableScope) ~ TABLE ~ tableName ~ opt(colList) ~ AS ~ subquery ~ withOrWithoutData ^^ { case _ ~ scope ~ _ ~ table_name ~ col_list ~ _ ~ query ~ withNoData => CreateAsQuery(scope, table_name, col_list, query, !withNoData) } private[syntax] lazy val tableScope: Parser[String] = globalOrLocal <~ TEMPORARY private[syntax] lazy val globalOrLocal: Parser[String] = ( GLOBAL ^^ { case _ => "GLOBAL" } | LOCAL ^^ { case _ => "LOCAL" } ) private[syntax] lazy val colList: Parser[List[String]] = LPAREN ~> columnNameList <~ RPAREN private[syntax] lazy val withOrWithoutData: Parser[scala.Boolean] = WITH ~> opt(NO) <~ DATA ^^ { case yesno => !yesno.isEmpty } private[syntax] lazy val tableElement: Parser[TableElement] = columnDefinition | tableConstraintDefinition private[syntax] lazy val columnDefinition: Parser[Column] = ident ~ opt(dataType) ~ opt(defaultClause) ~ opt(columnConstraintDefinition) ^^ { case name ~ datatype ~ defaultCl ~ colConsDef => Column(name, datatype, defaultCl, colConsDef)} private[syntax] lazy val defaultClause: Parser[Expression] = DEFAULT ~> defaultOption private[syntax] lazy val defaultOption: Parser[Expression] = ( NULLVAL ^^ { case _ => Null } | unsignedValueSpecification ) private[syntax] lazy val columnConstraintDefinition = opt(constraintName) ~ columnConstraint ^^ { case name ~ NotNull(_) => NotNull(name) case name ~ UniqueColumn(_, isPrim) => UniqueColumn(name, isPrim) case name ~ ReferencesColumn(_, table, refCols) => ReferencesColumn(name, table, refCols) case name ~ Check(_, search) => Check(name, search) } private[syntax] lazy val tableConstraintDefinition = opt(constraintName) ~ tableConstraint ^^ { case name ~ UniqueTable(_, isPrim, cols) => UniqueTable(name, isPrim, cols) case name ~ ReferencesTable(_, cols, table, refCols) => ReferencesTable(name, cols, table, refCols) case name ~ Check(_, search) => Check(name, search) } /////////////////////Constraints private[syntax] lazy val constraintName: Parser[String] = CONSTRAINT ~> ident private[syntax] lazy val columnConstraint: Parser[Constraint with InColumn] = ( notNull | uniqueSpecification | primaryKeySpecification | referencesSpecification | checkDefinition ) private[syntax] lazy val tableConstraint: Parser[Constraint with InTable] = uniqueDefinition | primaryKeyDefinition | referencesDefinition | checkDefinition private[syntax] lazy val notNull = NOT ~ NULLVAL ^^ { case _ => NotNull(None) } private[syntax] lazy val uniqueDefinition = UNIQUE ~ LPAREN ~> columnNameList <~ RPAREN ^^ { case list => UniqueTable(None, false, list) } private[syntax] lazy val primaryKeyDefinition = PRIMARY ~ KEY ~ LPAREN ~> columnNameList <~ RPAREN ^^ { case list => UniqueTable(None, true, list) } private[syntax] lazy val uniqueSpecification = UNIQUE ^^ { case _ => UniqueColumn(None, false) } private[syntax] lazy val primaryKeySpecification = PRIMARY ~ KEY ^^ { case _ => UniqueColumn(None, true) } private[syntax] lazy val referencesDefinition = FOREIGN ~ KEY ~ LPAREN ~> columnNameList ~ RPAREN ~ referencesSpecification ^^ { case cols ~ _ ~ ReferencesColumn(_, table, refCols) => ReferencesTable(None, cols, table, refCols) } private[syntax] lazy val referencesSpecification = REFERENCES ~> tableName ~ LPAREN ~ columnNameList <~ RPAREN ^^ { case table ~ _ ~ list => ReferencesColumn(None, table, list) } private[syntax] lazy val checkDefinition = CHECK ~ LPAREN ~> searchCondition <~ RPAREN ^^ { case s => Check(None, s) } ///////////////////////////// ///////////DELETE//////////// ///////////////////////////// private[syntax] lazy val deleteStatementSearched = DELETE ~ FROM ~> tableNameWithRename ~ opt(WHERE ~> searchCondition) ^^ { case table ~ search => Delete(table, search) } ///////////////////////////// /////////// DROP //////////// ///////////////////////////// private[syntax] lazy val dropTableStatement = DROP ~ TABLE ~> tableName ~ opt(dropBehaviour) ^^ { case name ~ behaviour => DropTable(name, behaviour) } private[syntax] lazy val dropBehaviour = CASCADE ^^ { case _ => "CASCADE" } | RESTRICT ^^ { case _ => "RESTRICT" } ///////////////////////////// ////////// UPDATE /////////// ///////////////////////////// private[syntax] lazy val updateStatementSearched = UPDATE ~> tableNameWithRename ~ SET ~ setClauseList ~ opt(WHERE ~> searchCondition) ^^ { case table ~ _ ~ list ~ search => Update(table, list, search) } private[syntax] lazy val setClauseList: Parser[List[SetClause]] = rep1sep(setClause, COMMA) private[syntax] lazy val setClause = ident ~ EQ ~ valueExpression ^^ { case name ~ _ ~ value => SetClause(name, value) } ///////////////////////////// ///////////INSERT//////////// ///////////////////////////// private[syntax] lazy val insertStatement = INSERT ~ INTO ~> tableName ~ insertColumnAndSource ^^ { case table ~ insert => Insert(table, insert) } private[syntax] lazy val insertColumnAndSource: Parser[InsertionData] = fromConstructor | fromSubquery | fromDefault private[syntax] lazy val fromSubquery: Parser[InsertionData] = opt(parenthesizedColumnNameList) ~ queryExpression ^^ { case cols ~ query => InsertionData.Subquery(cols, query) } private[syntax] lazy val fromConstructor: Parser[InsertionData] = opt(parenthesizedColumnNameList) ~ contextuallyTypedTableValueConstructor ^^ { case cols ~ row => InsertionData.Constructor(cols, row) } private[syntax] lazy val contextuallyTypedTableValueConstructor: Parser[List[Expression]] = VALUES ~> rep1sep(contextuallyTypedRowValueExpression, COMMA) private[syntax] lazy val contextuallyTypedRowValueExpression: Parser[Expression] = contextuallyTypedRowValueConstructor private[syntax] lazy val contextuallyTypedRowValueConstructor: Parser[Expression] = LPAREN ~> rep1sep(contextuallyTypedRowValueConstructorElement, COMMA) <~ RPAREN ^^ { case er => ExplicitRow(er) } private[syntax] lazy val contextuallyTypedRowValueConstructorElement = valueExpression | contextuallyTypedValueSpecification private[syntax] lazy val contextuallyTypedValueSpecification = ( DEFAULT ^^ { case _ => Default } | NULLVAL ^^ { case _ => Null } ) private[syntax] lazy val fromDefault: Parser[InsertionData] = DEFAULT ~ VALUES ^^ { case _ => InsertionData.DefaultValues() } ///////////////////////////// ///////////SELECT//////////// ///////////////////////////// private[syntax] lazy val querySpecification: Parser[Select] = SELECT ~> opt(setQuantifier) ~ selectList ~ fromClause ~ opt(whereClause) ~ opt(groupByClause) ~ opt(havingClause) ^^ { case setQ ~ select_list ~ from ~ where ~ groupBy ~ having => Select(setQ, select_list, from, where, groupBy, having) } private[syntax] lazy val selectList: Parser[List[DerivedColumn]] = ( ASTERISK ^^ { case _ => List() } | rep1sep(selectSublist, COMMA) ) private[syntax] lazy val selectSublist = derivedColumn ~ opt(ofClause) ^^ { case col ~ None => col case DerivedColumn(expr, as, _) ~ Some(d) => DerivedColumn(expr, as, d) } private[syntax] lazy val derivedColumn = valueExpression ~ opt(asClause) ^^ { case expr ~ as => DerivedColumn(expr, as) } private[syntax] lazy val asClause = AS ~> ident private[syntax] lazy val ofClause = OF ~> dataType private[syntax] lazy val fromClause = FROM ~> rep1sep(tableReference, COMMA) private[syntax] lazy val tableReference: Parser[Relation] = joinedTable | tableFactor private[syntax] lazy val tableFactor = tablePrimary private[syntax] lazy val tablePrimary = tableNameWithRename | LPAREN ~> joinedTable <~ RPAREN private[syntax] lazy val joinedTable: Parser[Relation] = qualifiedJoin private[syntax] lazy val qualifiedJoin = tableFactor ~ opt(joinType) ~ JOIN ~ tableReference ^^ { case left ~ join ~ _ ~ right => val jointype = join match { case None => JoinType.Inner case Some(t) => t } Jointure(left, right, jointype, None) } private[syntax] lazy val joinType = ( INNER ^^ { case _ => JoinType.Inner } | outerJoinType ~ opt(OUTER) ^^ { case t ~ _ => t } ) private[syntax] lazy val outerJoinType = ( LEFT ^^ { case _ => JoinType.Outer.Left } | RIGHT ^^ { case _ => JoinType.Outer.Right } | FULL ^^ { case _ => JoinType.Outer.Full } ) private[syntax] lazy val whereClause = WHERE ~> searchCondition private[syntax] lazy val groupByClause: Parser[List[Expression]] = GROUP ~ BY ~> repsep(completeColumnReference, COMMA) private[syntax] lazy val havingClause = HAVING ~> searchCondition private[syntax] lazy val setQuantifier = ( ALL ^^ { case _ => SetQuantifier.AllTuples } | DISTINCT ^^ { case _ => SetQuantifier.DistinctTuples } ) /////////////////////Names private[syntax] lazy val parenthesizedColumnNameList = LPAREN ~> columnNameList <~ RPAREN private[syntax] lazy val columnNameList: Parser[List[String]] = rep1sep(ident, COMMA) private[syntax] lazy val tableNameWithRename = tableName ~ opt(AS ~> ident) ^^ { case Table(schema, name, _) ~ as => Table(schema, name, as) } private[syntax] lazy val tableName = ident ~ opt(schemaName) ^^ { case name ~ None => Table(None, name, None) case schema ~ Some(table) => Table(Some(schema), table, None) } private[syntax] lazy val schemaName = PERIOD ~> ident /////////////////////Datatypes private[syntax] lazy val dataType = numericTypeDef | booleanTypeDef | characterStringType private[syntax] lazy val numericTypeDef: Parser[DataType] = ( SMALLINT ^^ { case _ => smallint } | INTEGER ^^ { case _ => integer } | BIGINT ^^ { case _ => bigint } | NUMERIC ~ LPAREN ~> numericLit <~ RPAREN ^^ { case n => numeric(n.asInstanceOf[Int]) } | NUMERIC ~ LPAREN ~> numericLit ~ COMMA ~ numericLit <~ RPAREN ^^ { case prec ~ _ ~ sca => numeric(prec.asInstanceOf[Int], sca.asInstanceOf[Int]) } | REAL ^^ { case _ => real } | DOUBLE ~ PRECISION ^^ { case _ => doublePrecision } ) private[syntax] lazy val booleanTypeDef = BOOLEAN ^^ { case _ => boolean } private[syntax] lazy val characterStringType = ( CHARACTER ~ LPAREN ~ numericLit ~ RPAREN ^^ { case n => character(n.asInstanceOf[Int]) } | CHARACTER ~ VARYING ~ LPAREN ~ numericLit ~ RPAREN ^^ { case n => characterVarying(n.asInstanceOf[Int]) } | CHARACTER ~ LARGE ~ OBJECT ^^ { case _ => characterLargeObject } ) /////////////////////SearchCondition private[syntax] lazy val booleanValueExpression: Parser[Expression] = booleanTerm ~ opt(OR ~> booleanValueExpression) ^^ { case term ~ None => term case term ~ Some(ex) => BinaryOperator(term, "OR", ex) } private[syntax] lazy val booleanTerm: Parser[Expression] = booleanFactor ~opt(AND ~> booleanTerm) ^^ { case factor ~ None => factor case factor ~ Some(ex) => BinaryOperator(factor, "AND", ex) } private[syntax] lazy val booleanFactor = opt(NOT) ~ booleanTest ^^ { case None ~ test => test case Some(_) ~ test => UnaryOperator(test, "NOT", true) } private[syntax] lazy val booleanTest = booleanPrimary ~ opt(isTruth) ^^ { case primary ~ None => primary case primary ~ Some(truth) => UnaryOperator(primary, truth, false) } private[syntax] lazy val isTruth = IS ~> opt(NOT) ~ truthValue ^^ { case None ~ tr => "IS " + tr case Some(_) ~ tr => "IS NOT " + tr } private[syntax] lazy val truthValue = ( TRUE ^^ { case _ => "TRUE" } | FALSE ^^ { case _ => "FALSE" } | UNKNOWN ^^ { case _ => "UNKNOWN" } ) private[syntax] lazy val booleanPrimary = predicate | booleanPredicand private[syntax] lazy val booleanPredicand = parenthesizedBooleanValueExpression | nonparenthesizedValueExpressionPrimary private[syntax] lazy val parenthesizedBooleanValueExpression = LPAREN ~> booleanValueExpression <~ RPAREN private[syntax] lazy val nonparenthesizedValueExpressionPrimary = nonparenthesizedValueExpressionPrimaryBis ~ repsep(ident, PERIOD) ^^ { case expr ~ Nil => expr case expr ~ (x :: xs) => xs.foldLeft[Expression](ExpressionField(expr, x))((accu, elem) => ExpressionField(accu, elem)) } private[syntax] lazy val nonparenthesizedValueExpressionPrimaryBis: Parser[Expression] = unsignedValueSpecification | columnReference | setFunctionSpecification | scalarSubquery | castSpecification | fieldReference //////////////////////////////////Predicates private[syntax] lazy val predicate = comparisonPredicate | betweenPredicate | inPredicate | nullPredicate private[syntax] lazy val comparisonPredicate = rowValuePredicand ~ compOp ~ rowValuePredicand ^^ { case left ~ op ~ right => BinaryOperator(left, op, right) } private[syntax] lazy val compOp = ( EQ ^^ { case _ => "=" } | DIFF ^^ { case _ => "<>" } | LT ^^ { case _ => "<" } | LE ^^ { case _ => "<=" } | GT ^^ { case _ => ">" } | GE ^^ { case _ => ">=" } ) private[syntax] lazy val betweenPredicate = rowValuePredicand ~ opt(NOT) ~ BETWEEN ~ rowValuePredicand ~ AND ~ rowValuePredicand ^^ { case v ~ None ~ _ ~ val1 ~ _ ~ val2 => BinaryOperator(BinaryOperator(v, ">=", val1), "AND", BinaryOperator(v, "<=", val2)) case v ~ Some(_) ~ _ ~ val1 ~ _ ~ val2 => BinaryOperator(BinaryOperator(v, "<", val1), "AND", BinaryOperator(v, ">", val2)) } private[syntax] lazy val inPredicate = rowValuePredicand ~ opt(NOT) ~ IN ~ inPredicateValue ^^ { case row ~ None ~ _ ~ in => BinaryOperator(row, "IN", in) case row ~ Some(_) ~ _ ~ in => BinaryOperator(row, "NOT IN", in) } private[syntax] lazy val inPredicateValue = subquery | LPAREN ~> inValueList <~ RPAREN private[syntax] lazy val inValueList: Parser[Expression] = rep1sep(rowValueExpression, COMMA) ^^ { case list => ExplicitRow(list) } private[syntax] lazy val nullPredicate = rowValuePredicand ~ IS ~ opt(NOT) <~ NULLVAL ^^ { case row ~ _ ~ None => UnaryOperator(row, "IS NULL", false) case row ~ _ ~ Some(_) => UnaryOperator(row, "IS NOT NULL", false) } //////////////////////////////////Nonparenthesized value expressions private[syntax] lazy val unsignedValueSpecification = ( string ^^ { case v => Constant(new CharacterString { val nativeValue = v }) } | smallintDef ^^ { case v => Constant(new value.ExactNumeric[Short] { val nativeValue = v val dataType = smallint }) } | intDef ^^ { case v => Constant(new ExactNumeric[Int] { val nativeValue = v val dataType = integer }) } | bigintDef ^^ { case v => Constant(new ExactNumeric[Long] { val nativeValue = v val dataType = bigint }) } | realDef ^^ { case v => Constant(new ApproximateNumeric[Float] { val nativeValue = v val dataType = real }) } | doubleDef ^^ { case v => Constant(new ApproximateNumeric[Double] { val nativeValue = v val dataType = doublePrecision }) } ) private[syntax] lazy val completeColumnReference: Parser[Expression] = columnReference ~ rep(PERIOD ~> ident) ^^ { case col ~ Nil => col case col ~ (x::xs) => xs.foldLeft[Expression](ExpressionField(col, x))((accu, elem) => ExpressionField(accu, elem)) } private[syntax] lazy val columnReference: Parser[Expression] = ident ~ opt(periodTableName) ^^ { case field ~ None => DirectField(None, None, field) case id ~ Some(Table(None, name, _)) => DirectField(None, Some(id), name) case id ~ Some(Table(Some(n), name, _)) => DirectField(Some(id), Some(n), name) } private[syntax] lazy val periodTableName = PERIOD ~> tableName private[syntax] lazy val setFunctionSpecification = aggregateFunction private[syntax] lazy val aggregateFunction = countFunctionDefinition | generalSetFunctionDefinition private[syntax] lazy val countFunctionDefinition = COUNT ~ LPAREN ~ ASTERISK ~ RPAREN ~> opt(filterClause) ^^ { case f => Aggregate("COUNT", SetFunction.Asterisk(), f) } private[syntax] lazy val generalSetFunctionDefinition = generalSetFunction ~ opt(filterClause) ^^ { case Aggregate(op, args, _) ~ filter => Aggregate(op, args, filter) } private[syntax] lazy val generalSetFunction = computationalOp ~ LPAREN ~ opt(setQuantifier) ~ valueExpression <~ RPAREN ^^ { case op ~ _ ~ setQuant ~ args => Aggregate(op, SetFunction.General(setQuant, args), None) } private[syntax] lazy val computationalOp = ( AVG ^^ { case _ => "AVG" } | MAX ^^ { case _ => "MAX" } | MIN ^^ { case _ => "MIN" } | SUM ^^ { case _ => "SUM" } | EVERY ^^ { case _ => "EVERY" } | ANY ^^ { case _ => "ANY" } | SOME ^^ { case _ => "SOME" } | COUNT ^^ { case _ => "COUNT" } ) private[syntax] lazy val filterClause = FILTER ~ LPAREN ~ WHERE ~> searchCondition <~ RPAREN private[syntax] lazy val searchCondition = booleanValueExpression private[syntax] lazy val scalarSubquery = subquery private[syntax] lazy val castSpecification = CAST ~ LPAREN ~> castOperand ~ AS ~ castTarget <~ RPAREN ^^ { case operand ~ _ ~ dataType => TypeCast(operand, dataType) } private[syntax] lazy val castOperand = valueExpression private[syntax] lazy val castTarget = dataType private[syntax] lazy val fieldReference = parenthesizedValueExpression private[syntax] lazy val rowValueExpression = explicitRowValueConstructor | nonparenthesizedValueExpressionPrimary //Original value was defined as follows // ::= | et // ::= | | private[syntax] lazy val rowValuePredicand = commonValueExpression ||| explicitRowValueConstructor ||| booleanPredicand private[syntax] lazy val explicitRowValueConstructor: Parser[Expression] = parenRowValueConstructor | subquery private[syntax] lazy val parenRowValueConstructor = LPAREN ~> valueExpression ~ COMMA ~ rowValueConstructorList <~ RPAREN ^^ { case value ~ _ ~ list => ExplicitRow(value::list) } private[syntax] lazy val rowValueConstructorList: Parser[List[Expression]] = rep1sep(valueExpression, COMMA) private[syntax] lazy val subquery = LPAREN ~> queryExpression <~ RPAREN private[syntax] lazy val queryExpression = queryExpressionBody private[syntax] lazy val queryExpressionBody: Parser[Expression] = queryTerm ~ opt(unionExcept ~ queryExpressionBody) ^^ { case q ~ None => q case left ~ Some(~(op, right)) => BinaryOperator(left, op, right) } private[syntax] lazy val unionExcept = ( UNION ^^ { case _ => "UNION" } | EXCEPT ^^ { case _ => "EXCEPT" } ) private[syntax] lazy val queryTerm: Parser[Expression] = queryPrimary ~ opt(INTERSECT ~> queryTerm) ^^ { case q ~ None => q case left ~ Some(right) => BinaryOperator(left, "INTERSECT", right) } private[syntax] lazy val queryPrimary = ( simpleTable | LPAREN ~> queryExpressionBody <~ RPAREN ) private[syntax] lazy val simpleTable: Parser[Expression] = ( querySpecification ^^ { case query => SelectExpression(query) } | explicitTable ) private[syntax] lazy val explicitTable = TABLE ~> tableName ^^ { case tableName => TableExpression(tableName) } private[syntax] lazy val valueExpression = booleanValueExpression ||| commonValueExpression ||| rowValueExpression private[syntax] lazy val commonValueExpression = numericValueExpression ||| stringValueExpression private[syntax] lazy val numericValueExpression: Parser[Expression] = term ~ opt(plusMinus ~ numericValueExpression) ^^ { case left ~ None => left case left ~ Some(op ~ right) => BinaryOperator(left, op, right) } private[syntax] lazy val plusMinus = ( PLUS ^^ { case _ => "+" } | MINUS ^^ { case _ => "-" } ) private[syntax] lazy val term: Parser[Expression] = factor ~ opt(starDiv ~ term) ^^ { case left ~ None => left case left ~ Some(op ~ right) => BinaryOperator(left, op, right) } private[syntax] lazy val starDiv = ( ASTERISK ^^ { case _ => "*" } | DIV ^^ { case _ => "/" } ) private[syntax] lazy val factor = opt(MINUS) ~ numericPrimary ^^ { case None ~ num => num case Some(_) ~ num => UnaryOperator(num, "-", true) } private[syntax] lazy val numericPrimary = nonparenthesizedValueExpressionPrimary | parenthesizedValueExpression private[syntax] lazy val stringValueExpression = characterValueExpression private[syntax] lazy val characterValueExpression: Parser[Expression] = characterFactor ~ opt(CONCAT ~> characterValueExpression) ^^ { case fac ~ None => fac case fac ~ Some(expr) => BinaryOperator(fac, "||", expr) } private[syntax] lazy val characterFactor = characterPrimary private[syntax] lazy val characterPrimary = nonparenthesizedValueExpressionPrimary | parenthesizedValueExpression //| stringValueFunction private[syntax] lazy val parenthesizedValueExpression = LPAREN ~> valueExpression <~ RPAREN }