1 / 32

Enhancements in SQL Development: Basic Functions, Filters, and Advanced Queries

This document details recent advancements in SQL development by the team comprised of Blake Kobelan, Brittany Blassingill, and Andrew Spence. Key functionalities now cover basic data types—Doubles, Dates, Longs, and Strings—and operators including Addition, Multiplication, and comparison operators. The team introduced ORDER BY, JOIN, OR, AND, and Subselect capabilities. Utilizing JSQLParser, an innovative visitor class was crafted to streamline the parsing process, allowing seamless recognition and processing of SQL queries and expressions.

hea
Télécharger la présentation

Enhancements in SQL Development: Basic Functions, Filters, and Advanced Queries

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Continue the Development of SQL in fJySwan Blake Kobelan, Brittany Blassingill, Andrew Spence

  2. Whatweadded • Basic functionality: • Doubles • Dates • Longs • Strings • Expressions: • Addition • Multiplication • < • > • !=

  3. Whatweadded • ORDER Bys • JOIN • OR • AND • ? - SUBSELECT

  4. Whatweinitiallydid • Instead of implementingthese changes in PyTuple.java, wecreated a visitor class thatusedJSQLParser and it’sowninternalgrammar to recognizefilters, tables, columns, etc.

  5. Whatweinitiallydid • Wealso made someminor changes in Python.g:

  6. ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’ • The JSQL Parser has a ‘temp’ global variable thatischangedbased on itsinternalgrammar. The parserwillautomaticallydetermine (via itsacceptmethod) which part of the SQL statementiswhat and passes itinto the appropriatevisitmethod. The temp variable isthenchanged. In this case, itwill first get the table name: Will call this temp = Persons

  7. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’ • It willthen check for a WHERE clause and goesthrough a series of visits:

  8. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’

  9. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’

  10. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’

  11. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’

  12. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’

  13. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’

  14. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’

  15. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’

  16. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’

  17. getWhere() visit(OrExpression) getLeftExpression() getRightExpression() visit(EqualsTo) visit(EqualsTo) getLeftExpression() getRightExpression() getLeftExpression() getRightExpression() visit(column) visit(String) visit(column) visit(String) temp = FirstName temp = ‘Tove’ temp = FirstName temp = ‘Ola’ ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’ Will do the samething for the right hand side

  18. ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’ • wasEqualsiseventually set to false sincetherewere no matches and the OR clauses are interpreted as filters: • Finally, the columns are added:

  19. ‘’SELECT * FROM Persons WHERE FirstName=‘Tove’ OR FirstName=‘Ola’’’ • Final test output before RDF conversion:

  20. Conversion to RDF • Our conversion takes place insideourSQLVisitor class. • As a smallexample: SELECT * FROM NEWTEST1 • Based on what isseen, a string getsconsistentlybuiltuntilit’s in the correct RDF format:

  21. SELECT * FROM NEWTEST1

  22. SELECT * FROM NEWTEST1 • SELECT sub, pred, objfrom table( SEM_MATCH(SELECT ?sub ?pred ?obj WHERE (?sub ?pred ?obj . )’. SEM_Models(‘NEWTEST1_CS345_RICK), null, SEM_ALIASES(SEM_ALIAS(‘tbl’, ‘www.example.org/NEWTEST1/’)), null))

  23. Implementation in PyTuple.java • Sinceweused a visitor class, implementationwaspretty simple: • Wejust made a visitorobject and passed in the sqlstatement,

  24. Concepts Covered

  25. Concepts Covered

  26. Concepts Covered

  27. Concepts Covered

  28. Concepts Covered

  29. Concepts Covered

  30. Concepts Covered

  31. Concepts Covered

  32. Concepts Covered

More Related