• Introduction
• JSQL formal grammar
• Arrays
• Strings
• References
• Functions
• Query optimization
• Select method
• Example

Introduction

1. There are no joins of several tables and nested subqueries. Query always returns set of objects from one table.
2. Standard C types are used for atomic table columns.
3. There are no NULL values, except null references. I am completely agree with C.J. Date critics of three-value logic and his proposal to use default values instead.
4. Arrays can be used as record components. Special exists quantor is provided for locating element in arrays.
5. User methods can be defined for table records (objects) as well as for record components.
6. References between objects are supported including automatic support of inverse references.
7. As far as query language is deeply integrated with Java language, case sensitive mode is used for language identifiers as well as for keywords.
8. No implicit conversion of integer and floating types is done to string representation. If such conversion is need, it should be done explicitly.

JSQL formal grammar

Grammar conventions

Example	Meaning
expression	non-terminals
not	terminals
|	disjoint alternatives
(not)	optional part
{1..9}	repeat zero or more times

select-condition ::= ( expression ) ( traverse ) ( order )
expression ::= disjunction
disjunction ::= conjunction 
        | conjunction or disjunction
conjunction ::= comparison 
        | comparison and conjunction
comparison ::= operand = operand 
        | operand != operand 
        | operand <> operand 
        | operand < operand 
        | operand <= operand 
        | operand > operand 
        | operand >= operand 
        | operand (not) like operand 
        | operand (not) like operand escape string
        | operand (not) in operand
        | operand (not) in expressions-list
        | operand (not) between operand and operand
	| operand is (not) null
operand ::= addition
additions ::= multiplication 
        | addition +  multiplication
        | addition || multiplication
        | addition -  multiplication
multiplication ::= power 
        | multiplication * power
        | multiplication / power
power ::= term
        | term ^ power
term ::= identifier | number | string 
        | true | false | null 
	| current | first | last
	| ( expression ) 
        | not comparison
	| - term
	| term [ expression ] 
	| identifier . term 
	| function term
        | exists identifier : term
function ::= abs | length | lower | upper
        | integer | real | string | 
        | sin | cos | tan | asin | acos | 
        | atan | log | exp | ceil | floor 
string ::= ' { { any-character-except-quote } ('') } '
expressions-list ::= ( expression { , expression } )
order ::= order by sort-list
sort-list ::= field-order { , field-order }
field-order ::= field (asc | desc)
field ::= identifier { . identifier }
fields-list ::=  field { , field }
user-function ::= identifier

Identifiers are case sensitive, begin with a..z, A..Z, '_' or '$' character, contain only a-z, A..Z, 0..9 '_' or '$' characters, and do not duplicate a SQL reserved words.

List of reserved words

abs	acos	and	asc	asin
atan	between	by	cos	ceil
current	desc	escape	exists	exp
false	floor	in	integer	is
length	like	log	lower	not
null	or	real	sin	string
tan	true	upper

JSQL extends ANSI standard SQL operations by supporting bit manipulation operations. Operators and/or can be applied not only to boolean operands but also to operands of integer type. Result of applying and/or operator to integer operands is integer value with bits set by bit-AND/bit-OR operation. Bits operations can be used for efficient implementation of small sets. Also rasing to a power operation ^ is supported by JSQL for integer and floating point types.

Arrays

1. It is possible to get the number of elements in the array by length() function.
2. Array elements can be fetched by [] operator. If index expression is out of array range, then exception will be raised.
3. Operator in can be used for checking if array contains value specified by left operand. This operation can be used only for arrays of atomic types: with boolean, numeric, reference or string components.
4. Iteration through array elements is performed by exists operator. Variable specified after exists keyword can be used as index in arrays in the expression preceded by exists quantor. This index variable will iterate through all possible array index values, until value of expression will become true or index runs out of range. Condition

           exists i: (contract[i].company.location = 'US')
   

   will select all details which are shipped by companies located in US, while query

           not exists i: (contract[i].company.location = 'US')
   

   will select all details which are shipped only from companies outside US.

   Nested exists clauses are allowed. Using of nested exists quantors is equivalent to nested loops using correspondent index variables. For example query

           exists colon: (exists row: (matrix[colon][row] = 0))
   

   will select all records, containing 0 in elements of matrix field, which has type array of array of integer. This construction is equivalent to the following two nested loops:

          bool result = false;
          for (int colon = 0; colon < matrix.length(); colon++) { 
               for (int row = 0; row < matrix[colon].length(); row++) { 
   	         if (matrix[colon][row] == 0) { 
                        result = true;
   		     break;
                    }
               }
          }
   

   Order of using indices is significant! Result of the following query execution

           exists row: (exists colon: (matrix[colon][row] = 0))
   

   will be completely different with result of previous query. The program can simply hang in last case due to infinite loop for empty matrices.

Strings

Construction like can be used for matching string with a pattern containing special wildcard characters '%' and '_'. Character '_' matches any single character, while character '%' matches any number of characters (including 0). Extended form of like operator with escape part can be used to handle characters '%' and '_' in the pattern as normal characters if they are preceded by special escape character, specified after escape keyword.

It is possible to search substring within string by in operator. Expression ('blue' in color) will be true for all records which color fields contains 'blue' word. Strings can be concatenated by + or || operators. Last one was added only for compatibility with ANSI SQL standard. As far as JSQL doesn't support implicit conversion to string type in expressions, semantic of operator + can be redefined for strings.

References

        company.address.city = 'Chicago'

References can be checked for null by is null or is not null predicates. Also references can be compared for equality with each other as well as with special null keyword. When null reference is dereferenced, exception is be raised by JSQL.

There is special keyword current, which can be used to get reference to current record during table search. Usually current keyword is used for comparison of current record identifier with other references or locating it within array of references. For example, the following query will search in Contract table for all active contracts (assuming that field canceledContracts has Contract[] type):

        current not in supplier.canceledContracts

Programmer can define methods for structures, which can be used in queries with the same syntax as normal structure components. Such methods should have no arguments except pointer to the object to which they belong (this pointer in Java), and should return atomic value (of boolean, numeric, string or reference type). Also method should not change object instance (immutable method). So user-defined methods can be used for creation virtual components - components which are not stored in database, but instead if this are calculated using values of other components. For example, you can use standard Java java.util.Date class with such methods as getYear(), getMonth()... So it is possible to specify queries like: "delivery.getYear = 99" in application, where delivery record field has java.util.Date type.

Functions

Query optimization

JSQL invokes iterator's method getByKey. Programmer is responsible for managing index (it can be implemented for example by Java Hashtable class) and should provide implementation of getByKey method which should search object by given key. JSQL passes name of the field and value of the key to getByKey method. So it is possible to check if index exists for specified field and lookup object in the index using key value. If index doesn't exists for this field, then NoIndexException should be raised by getByKey method. Otherwise method should return located object or null if object with specified value of the key was not found.

Select method

    public Object[] select(String className, QueryIterator iterator,
			   String query, int limit, int nThreads)

className

string with name of the class, which instances will be selected by the query. Query can select only object of this class or objects derived from this class.

iterator

Object implementing ObjectQuery interface. This interface declares two methods:

        public abstract Object getFirst();
        public abstract Object getNext(Object prevObj, int prevIndex);
        public Object getByKey(String primaryKey, Object keyValue) 
            throws NoIndexException;

The method getFirst is used to obtain reference to the first object. The method getNext is used to get references to the following objects. Iterator should return object of class specified by className string or derived from this class, otherwise runtime exception will be thrown. To terminate query execution iterator should return null reference.

The method getByKey can be used to provide fast access to the object by unique key value. This method should throw exception NoIndexException if the field primaryKey is not indexed. Otherwise the method should locate object using keyValue and return object reference or null if object was not found.

If multithreaded query is issued, then iterator class should be state-less, because it can be accessed concurrently by different threads. To make such state-less implementation possible, reference to previous object as well as number of this object is passed to to getNext method. Objects are enumerated starting from 0 for the first object. If query is single-threaded, there are no restrictions on iterator implementation.

query

Query selection condition. It should be boolean expression with optional order by part.

limit

Optional parameter specifying limitation for number of selected objects. For example if this parameter is set to 1, then no more than one object will be selected and query execution will be terminated immediately after locating first object for which search predicate is true. Default value of this parameter is zero, which disables any limitations on number of selected records.

nThreads

Optional parameter specifying number of threads created for query execution. JSQL is able to split query execution into several parallel threads of control, each of which performs tests for its own records and then results of all threads are merged. Default value of this parameter is one. If limit parameter is not zero, then also only one thread is used for query execution.

To split table scan, JSQL starts N threads each of them tests N-s objects returned by iterator (i.e. thread number 0 tests objects 0,N,2*N,... thread number 1 tests objects 1,1+N,1+2*N,... and so on). Each thread builds its own list of selected objects. After termination of all threads, these lists are concatenated to construct the single result array.

If result should be sorted, then each thread, after the end of object scanning, sorts the objects it selected. After termination of all threads, their lists are merged (as it is done with external sort).

Returns

This method returns array of selected objects. Type of the result array is specified by className parameter.

Throws

This method throws CompileError exception when specified query string contains parse or syntax errors. Also exception can be thrown during query execution as a result of invalid operation: such as accessing null reference, index out of bounds, function domain error, division by zero.

Example

Look for new version at my homepage | E-Mail me about bugs and problems