CSE-4/562 Spring 2019 - Practicum: Checkpoint 0

Practicum: Checkpoint 0

CSE-4/562 Spring 2019

February 01, 2019

Textbook: Ch. 16.1
You write code You push to GIT Hit Submit DµBStep compiles π s Run You get emailed Try   again

Query Processor

SQL Query Parsed SQL Rel. Alg. Statistics Optimized RA Execution Plan Data Query Results JSQLParser Checkpoint 0

Checkpoint 0

"Hello World" due Feb 8

                           SELECT * FROM FOO
                               cat FOO

Getting Started

A link to the submission system is on the syllabus.

There's a handy "create account" link.

Use your UBIT email address to create an account.

TODO By Feb 8

  1. Create a group of up to 3 people
  2. Register your group
  3. Access your group's GIT repository
  4. Download JSQLParser
  5. Look at the JSQLParser Javadoc
  6. Read the Checkpoint 0 Overview
  7. Create a SQL "Hello World" program
  8. Hit Submit

A SQL "Hello World"

  1. Read SELECT * FROM [tablename] from System.in
  2. Use JSQLParser to parse the query
    • You don't need to use JSQLParser... but it will make your life easier later.
  3. Get the Table Name
  4. Read in the file data/[tablename].csv
  5. Write the contents out to System.out

Submit as many times as you need to

Your grade will never ever decrease because you decided to submit just one more time

Using CCJSqlParser

	// StringReaders create a reader from a string 
	Reader input = new StringReader("SELECT * FROM R")

	// CCJSqlParser takes a Reader or InputStream
	CCJSqlParser parser = new CCJSqlParser(input)

	// CCJSqlParser.Statement() returns the next 
	// complete Statement object from the reader or 
	// input stream (or null if the stream is empty).
	Statement statement = parser.Statement()

Using CCJSqlParser

	// System.in is an InputStream
	CCJSqlParser parser = new CCJSqlParser(System.in)

	Statement statement = parser.Statement();
	// loop until you hit the last statement
	while(statement != null){

	  // Do something with statement

	  // ... then read the next statement
	  statement = parser.Statement();

Abstract Syntax Trees

  • Statement: Select, CreateTable
  • SelectBody: PlainSelect, Union
  • FromItem: Table, Join, SubSelect
  • SelectItem: AllColumns, AllTableColumns, SelectExpressionItem
  • Expression: LongValue, AddExpression, GreaterThan

  while(statement != null){
    if(statement instanceof Select){
      Select select = (Select)statement;
      // Do something with `select`

    } else if(statement instanceof CreateTable){
      CreateTable create = (CreateTable)statement;
      // Do something with `create`

    } else {
      throw new SqlException("Can't handle: "+statement);
    statement = parser.Statement()

	Select select = (Select)statement;
	SelectBody body = select.getSelectBody();
	if(body instanceof /* ... */){
	  // ...
  • PlainSelect: (SELECT * FROM ...)
  • Union: (SELECT * FROM ...) UNION ALL (SELECT ...)

	Select select = (Select)statement;
	SelectBody body = select.getSelectBody();
	if(body instanceof PlainSelect){
	  PlainSelect plain = (PlainSelect)body;
	  // Do something with `plain`


	SELECT [distinct] [selectItems]
	FROM [fromItem], [joins, ...]
	WHERE [where]
	GROUP BY [groupByColumnReferences]
	HAVING [having]
	ORDER BY [orderByElements]
	LIMIT [limit]
Everything in [brackets] has a method in PlainSelect