Introduction

JSQL is subset of SQL languages, which can be used to select objects instances according to selection condition. JSQL can be used for implementing personal minidatabase, as tool for dynamic compilation and execution of user requests. JSQL uses notation more popular for object-oriented programming then for relational database. Table rows are considered as object instances and the table - as class of these objects. Unlike SQL, JSQL is oriented on work with objects instead of SQL tuples. So the result of each query execution is a set of objects of one class. The main differences of JSQL from standard SQL are:

  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

The following rules in BNF-like notation specifies grammar of JSQL query language search predicate:

Grammar conventions
ExampleMeaning
expressionnon-terminals
notterminals
|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
absacosandascasin
atanbetweenbycosceil
currentdescescapeexistsexp
falsefloorinintegeris
lengthlikeloglowernot
nullorrealsinstring
tantrueupper

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

JSQL is able to work with Java array types. JSQL provides a set of special constructions for dealing with 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

All strings in JSQL have varying length and programmer should not worry about specification of maximal length for character fields. All operations acceptable for arrays are also applicable to strings. In addition to them strings have a set their own operations. First of all string can be compared with each other using standard relation operators. At the current moment JSQL supports only ASCII character set (corresponds to type char in C) and byte-by-byte comparison of strings ignoring locality settings.

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

References can be dereferenced using the same dot notation as used for accessing structure components. For example the following query
        company.address.city = 'Chicago'
will access record referenced by company component of Contract record and extract city component of address field of referenced record from Supplier table.

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

Predefined functions
NameArgument typeReturn typeDescription
absintegerintegerabsolute value of the argument
absrealrealabsolute value of the argument
sinrealrealsin (rad)
cosrealrealcos (rad)
tanrealrealtan (rad)
asinrealrealarcsin
acosrealrealarccos
atanrealrealarctan
exprealrealexponent
logrealrealnatural logarithm
ceilrealrealthe smallest integer value that is not less than the argument
floorrealrealthe largest integer value that is not greater than the argument
integerrealintegerconversion of real to integer
lengtharrayintegernumber of elements in array
lowerstringstringlowercase string
realintegerrealconversion of integer to real
stringintegerstringconversion of integer to string
stringrealstringconversion of real to string
upperstringstringuppercase string

Query optimization

JSQL provides mechanism for fast location of object by unique primary key. When query condition is check for equality of scalar (numeric or string) field and constant value or query condition is conjunction (logical AND) of operands and left operand is check for equality of scalar field and constant value then JSQL tries to apply index to locate object by this key value. If object was found but query condition is is conjunction, then JSQL also checks that value of right operand is true.

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

Class Query provides single public method select with the following profile:
    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

File Test.java contains example of application using JSQL. This application illustrates using of various types of database queries. Classical Detail/Contract/Supplier database scheme is used in this example. Program stores and retrieves data using Java serialization mechanism. Class Record in this example links table rows in L2-list and provides iterator for navigation through the table rows.


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