ECS 165B Spring 2010 - Database System Implementation

DavisDB Part 4: Query Engine Component (due Friday, 6/4 at 11:59pm)

Introduction

select <attrName>, ... , <attrName>
       from <relName>, ..., <relName>
       [ where <attrName> <cmpOp> <attrOrValue> and ... 
         and <attrName> <cmpOp> <attrOrValue> ] ;
insert into <relName> values (<value>, ..., <value>) ;
delete from <relName> [ where <attrName> <cmpOp> <attrOrValue> and
                       ... and <attrName> <cmpOp> <attrOrValue>] ;
update <relName> set <attrName> = <attrOrValue>
       [ where <attrName> <cmpOp> <attrOrValue> and ... 
         and <attrName> <cmpOp> <attrOrValue> ] ;

The Select Command

  select <attrName>, ... , <attrName>
  from <relName>, ..., <relName>
[ where <attrName> <cmpOp> <attrOrValue> and ... and <attrName> <cmpOp> <attrOrValue> ] ;

• Each <attrName> in the select clause is fully-qualified, i.e., it includes the relation name. (Hence you have to say, e.g., select R.A from ... rather than just select A from ....)
• The list of relation names in the from clause does not contain any duplicates, as self-joins are not supported. (This must be checked by your QueryEngine implementation, see below.)
• The where clause is optional, and each term in the where clause is of the form <attrName> <cmpOp> <attrOrValue> where
  • <attrName> is a fully-qualified attribute name,
  • <cmpOp> is one of =, <, >, <=, >=, or <>, and
  • <attrOrValue> is either a fully-qualified attribute name or a constant value. Constant values may be either integers, floats (which must contain a decimal point to be recognized as floats by the parser), or strings enclosed in quotation marks.

The Insert Command

insert into <relName> values (<value>, ..., <value>);

The Delete Command

delete from <relName> [ where <attrName> <cmpOp> <attrOrValue> and
                       ... and <attrName> <cmpOp> <attrOrValue>] ;

The Update Command

update <relName> set <attrName> = <attrOrValue>
       [ where <attrName> <cmpOp> <attrOrValue> and ... 
         and <attrName> <cmpOp> <attrOrValue> ] ;

For a fuller description of each of the QueryEngine methods listed above, please read the online documentation carefully. As in Parts 1-3, you must not change any of the definitions of the public methods we have provided, as this will break our automated tests. However, you are free to add new methods to the classes (public or private) as you see fit.

Implementation Guidelines and Suggestions

Basic requirements. Your implementation of QueryEngine::select should build a physical query plan as discussed in lecture, then execute the plan. We strongly suggest you use a pipelined evaluation model, although you may use a temporary relation approach instead of you have valid reasons for doing so.

Regardless of other design decisons you make, we would like your query execution strategy to be able to take advantage of indices whenever it is (relatively) straightforward to do so. In particular:

• Suppose the query includes a local selection condition of the form R.A=v, where v is a constant value. If relation R has an index on attribute A, then an IndexScan together with calls to RecordFileHandle::getRecord can be used to fetch the tuples of R, rather than using a RecordFileScan.
• You may use a simple nested-loops algorithm for each join or cross-product. Suppose you are joining R with S based on attribute equality, suppose S has no local selection condition, and suppose S has an index on its join attribute A. Then you can make S the inner relation of the nested-loop join and use an index scan on S instead of a file scan. (You need not, however, attempt to reorder the relations in the query in order to make such index joins possible.)

Although the execution strategies for delete and update commands generally are simpler than for select commands, you should still build physical query plans for delete. You will probably find that some physical operators from your implementation of select can be reused. Query plans are not required for insert commands.

Printing query plans. Your system should provide a way to pretty-print physical query plans for select, delete, and update commands before the command is executed. You will probably want to render tree-structured plans using some form of indentation. Query plans should be printed only when the value of parameter queryplans is set to on. This can be controlled via another addition to SystemParser in the form of a generic command

set <param> = <value>; 

IQueryOperator. We are providing an architectural template for a query evaluation engine implementation in the form of a generic C++ interface for query operators, IQueryOperator, and sample implementations of two operators, ProjectOperator and FileScanOperator. The skeleton code you will receive for QueryEngine demonstrates how to construct and execute pipelined execution plans based on this interface for projection-only queries. The use of IQueryOperator is not required, and you are free to use or ignore these files and sample code (or rewrite them to suit your needs). If you do choose to follow this template, note that you may also need to extend IQueryOperator with additional methods, or introduce new interfaces deriving from IQueryOperator for situations where additional functionality is required. See the notes for Lecture 21 for more information.

Extra Credit Opportunities

• Small amount of extra credit. Improve on the "canonical execution plans" methodology described above by figuring out some heuristics and using them to produce more efficient plans for queries and updates. You must document what you have done in the writeup.
• Small amount of extra credit. Exceptionally clean architecture and code.
• Large amount of extra credit. Implement a full-blown System R-style query optimizer, as described in lecture and in the textbook. There will be a variable amount of credit given for this option, depending on how much you do. This will involve considering different join orders and performing some sort of cost estimation to compare execution plans, using a dynamic programming strategy. Cost estimation will require at a minimum maintaining and using cardinality statistics for each relation.

In addition to the source files, you will be provided with a sample DavisDB "script", Queries.ddb, that exercises the basic functionality of the query engine. This can be "run" using

./DavisDB < Queries.ddb

As usual, you will be expected to also create additional tests yourself, empahsizing corner cases and error handling.

Documentation

Before you begin coding, please answer the first question in the text file, which asks you to estimate the total number of person-hours you think will be needed to implement your solution (including testing).

Before submitting your code using submit.sh 4, please answer the the second question, which asks how many person-hours of effort were actually required. Also, if you tackled any of the extra credit opportunities mentioned above, don't forget to describe what you did in the writeup. Be sure to give sufficient detail so that we can figure out how much credit to give you.

Submission and Grading

Also, please remove or disable any printf debugging statements in your code before submitting, as these interfere with interpretation of the shell output. (At this point, you should be using the debugger nearly all of the time anyway!)

Acknowledgments