Provides a set of ParserElement-derived classes that build SQL data retrieval statements. This package also provides a class for building a good join path among all the tables in a user selection based on the joins provided by the form.

MRALD Form Building Restrictions imposed by MraldDijkstra

An MRALD form represents a set of database tables and a unique way of joining them together to retrieve data around a certain concept. Trying to force a single form to do more than this will cause unpredictable or incorrect results (it is possible to build an MRALD form that will build mulitple queries and therefore bypass these issues, however these forms must be hand built). Therefore, when more than one table is listed in an MRALD form, each listed table must be connected to the others by a single, acyclic path. There are two problems that give rise to this restriction:

Incomplete join path

This occurs when there is not a complete join path amongst all the tables included on the form. The result of an incomplete join path is a cross-product. This will result in a set containing every possible combination of the records in the pair of relations being joined without regard to the context of the relationships. Such a result is basically useless in terms of retrieving useful data from normal data models.

Non-unique join path

This occurs when there is more than one join path between any two tables. While the query built will be valid and not produce a cross-product, which join path to use, and therefore the query result, is indeterminate. Should MRALD use both paths, or one? And if only one, which one? When creating an MRALD form, the user should have a single concept of how these tables are to be joined and all other join paths should be set to be ignored in the second step in the form building process.

Below is the specifics for each SqlElement:

General rules in MRALD HTML tags

Standard MRALD names in HMTL forms

Tag What it means Required?
Table Name of the table to be used. Yes, unless Count(*)
Synonym Synonym to be used instead of the table name. If used properly, this can be used to have multiple "copies" of a table in a single query. No
Field Name of the field to be used. Yes, unless Count(*)
As Synonym to be used instead of the table name. This can be used to put a decent name on special elements, such as ConcatElements or stats. No
Order Order in which this item is to be inserted into the query. It is not necessary for the Orders to be sequential. Yes, and unique, or fields in the output will be overwritten

StatElement tag structure. The StatElement is responsible for adding statistical functions to queries.

Tag What it means Required?
Table standard Yes, unless Count(*)
Syn standard No
Field standard Yes, unless Count(*)
As standard No
GroupSelect Yes
Group Yes
Order standard Yes, and unique, or fields in the output will be overwritten

StaticSelectElement tag structure. The StatElement is responsible for adding a static string to the SELECT clause without modification.

Tag What it means Required?
Table standard Yes, unless Count(*)
Syn standard No
Value Value will be added to the SELECT clause exactly as specified. Yes.

This is the validity check for the OrFilterElementTest

Is operator IS NULL

Is value empty

o1

o2

v

v1

v2

valid?

Y

Y

Y

Y

Y

y

If both operators are "IS NULL" then the values don't matter

Y

Y

Y

Y

N

y

Y

Y

Y

N

Y

y

Y

Y

Y

N

N

y

Y

Y

N

Y

Y

y

Y

Y

N

Y

N

y

Y

Y

N

N

Y

y

Y

Y

N

N

N

y

Y

N

Y

Y

Y

y

Value is a fallback - if it has a value, it's OK

Y

N

Y

Y

N

y

Y

N

Y

N

Y

y

Y

N

Y

N

N

y

Y

N

N

Y

Y

y

if both values are provided - all is well

Y

N

N

Y

N

n

missing value is where operator is not a  NULL - BAD

Y

N

N

N

Y

n

missing value is where operator is not a  NULL - BAD

Y

N

N

N

N

n

nobody has a value and at least on op is not a NULL - BAD

N

Y

Y

Y

Y

y

Value is a fallback - if it has a value, it's OK

N

Y

Y

Y

N

y

N

Y

Y

N

Y

y

N

Y

Y

N

N

y

N

Y

N

Y

Y

y

if both values are provided - all is well

N

Y

N

Y

N

y

missing value is where operator is NULL - all is well

N

Y

N

N

Y

n

missing value is where operator is not a  NULL - BAD

N

Y

N

N

N

n

nobody has a value and at least on op is not a NULL - BAD

N

N

Y

Y

Y

y

Value is a fallback - if it has a value, it's OK

N

N

Y

Y

N

y

N

N

Y

N

Y

y

N

N

Y

N

N

y

N

N

N

Y

Y

y

if both values are provided - all is well

N

N

N

Y

N

n

missing value is where operator is not a  NULL - BAD

N

N

N

N

Y

n

missing value is where operator is not a  NULL - BAD

N

N

N

N

N

n

nobody has a value and at least on op is not a NULL - BAD