X Tutup
Detailed Design Document for the new tinySQLWhere Class

Introduction

Where clause processing in tinySQL is currently done after a complete record is ready for output.  For multi-table joins tinySQL may have to scan thousands of rows in order to build the candidate record, only to find that the first where clause fails.  This makes the process very inefficient for even small databases. 

Where clause processing in tinySQL uses an if then else mthodology to handle individual column=condition elements in the where clause.  This makes it difficult to process complex where clauses and virtually impossible to implement and/or combinations.


Code Structure and Components

A new tinySQLWhere class will be developed which will centralize all of the processing of where clauses for tinySQL.  Where clause parsing code will be part of the tinySQLWhere constructor, which will be instantiated by tinySQLParser when a WHERE clause is encountered. 

This code will produce a whereClausList Vector.  Each element of the whereClauseList is itself a Vector containing elements which are either whereConditions or new tinySQLWhere objects.

Each whereCondition is a Vector containing the following 8 elements;

0   The type of phrase - currently always JOIN
1   The name of the left side of the condition, usually a column name or function
2   The types of condition (=, !=, like)
3   The right side of the condition, a column or constant
4   The type of the left side of the condition (INT, FLOAT, CHAR, DATE)
5   The current value of the left side of the condition
6   The type of the right side of the condition (INT, FLOAT, CHAR, DATE)
7   The current value of the right side of the condition
8   The current state of the entire whereCondition - TRUE, FALSE, or UNKNOWN
 
The implementation of recursive parsing will allow nested where clauses such as in the example below;

select column1 from table1 where column1=condition1 and (column2=condition2 or (column3=condition3 and column4=condition4));

The whereClauseList for the above where clause is a Vector containing a single element, having the following structure;

whereClauseList element 0  is a Vector containing 2 elements;
  1. the whereCondition column1=condition1
  2. a tinySQLWhere object for the portion of the where clause in brackets.  This object will have a whereClauseList consisting of 2 elements
    1. The first element will be a Vector having a single element, the whereCondition column2=condition2
    2. The 2nd element will be a Vector having 2 elements;  the whereConditions column3=condition3 and column4=condition4
This structure will allow the following simple rules to apply when the where clause is being evaluated;
In the example listed above, if column1 != condition1, the entire where clause is false.  If column2=condition2, then the tinySQLWhere object that represents the portion of the where clause in brackets is TRUE.

The tinySQLWhere class will have a method named evaluate which will take a column name and value as arguments.  This method will then scan all the elements of the whereClauseList and will recursively evaluate the conditions.  Using the rules listed above, the where clause as a whole will be evaulated with as little column information as possible.

The tinySQLWhere class will have a clearValues method which will reset all of the values in the whereConditions to null in order to start processing a new row.

X Tutup