esProc Simplifies SQL-style Computations – Multi-level Relationships

Multi-level relationships are one of the complicated SQL-style computations which we often need to deal with during database application development. The relatively abstract SQL JOIN statement is suitable for expressing simple relationships between tables, but once the multi-level relationships are involved, the code becomes rather complicated. esProc uses object references to express the relationships, thus its code is easier to read. The following example will illustrate this.

Table channel stores the correspondence between a certain website's all channels and their parent channels, which are respectively represented by id field and parent field. There are four levels of correspondence at most and root represents the website itself (i.e. the root node). Please list a certain channel's next level, next two and three levels of channels, which will be separated from each other by commas, according to the input parameters. Some of the data of channel are as follows:

esProc code:

A1: Query channel and name the selected data as data, part of which are as follows:

B1: =data.switch(parent,data:id). This line of code establishes a self-join. switch function is used to switch the parent field to the corresponding records in data, as shown below:

After the switch, parent.id can be used to refer to a parent channel, and parent.parent.parent.id represents the channel three levels up. This kind of self-join can be expressed with SQL JOINs, but confusion could arise when there are many levels of relations.

A2: =create(id,level,sub). This line of code creates an empty table sequence for storing the final computed result. Different from data - the explicit definition variable, cell name A2 is by default the variable name of the table sequence. The current value of A2 is as follows:

A3: =data.select(parent.id==arg1 ). This line of code selects records in which the parent channel is equal to parameter arg1, that is, the next-level channel of arg1, from data. arg1 is a pre-defined external parameter, which can be got from a Java program or a report. Suppose the value of arg1 is p1, then the result of A3 is as follows:

B3: =A2.insert(0,arg1,1,A3.(name).string()). This line of code inserts a record into the table sequence in A2. The record's first field value is arg1 (whose value is supposed to be p1), the second one is 1, which represents the first-level sub-channel, and the third one is an expression A3.(name).string(), which represents drawing out the column - name - from A3 and concatenating the column data into stings which separated from each other by commas. The result of B3 is as follows:

A4: =data.select(parent.parent.id==arg1). This line of code is similar to that in A3. It selects from data the next two levels of channels of arg1. Result is as follows:

A5 is similar to A4 by selecting the next three levels of channels of arg1. By doing so, the next N levels of channels will be selected.

As what was done in B3, both B4 and B5 insert new records into the table sequence in A2, with the value of level field being 2 and 3 respectively. When B5 is executed, the final result of this operation can be seen in A2:

If the value of arg1 is c11, the final result will be as follows:

Sometimes more detail data is wanted. For example, list all sub-channels of a certain channel and marking the cascade relationships between them. This operation can be realized using the following code:

The modified code is colored in red. The code in B3 becomes =A2.insert(0,arg1,1,A3), meaning storing the records in A3 in A2 directly. Suppose the value of arg1 is p1, the result will be as follows:

Click the record in sub field and details will be displayed:

It can be seen that the field values in esProc is of genericity, which can store a set of records or a single record. Please note the essential role of switch function is to switch the foreign key to a single record in the primary table.

When B5 is executed, the result of A2 is as follows:

A6: =A2.(~.sub.new(A2.id,A2.level,id:subid,name)). This line of code joins values of id field and level field to members of each set of records of sub field in A2. A2.() means performing computation on A2, "~" represents each record in A2 and ~.sub represents the sub field of each record (a set of records). new function is used to generate a new table sequence which consists of the id field, level field as well as the id field and name field of sub field. The computed result of A6 is as follows:

A7: =A6.union(). This line of code concatenates all groups of records in A6 together to form the final result:

In some other occasions, all sub-channels of each channel need to be listed directly rather than using parameters. This operation can be realized with esProc's for statement. The corresponding code is shown below:

for data.(id) in A3 means fetching each record of id field of data by loop. The loop variable can be represented by cell A3 where for statement settles. The working scope of a loop statement is determined by the indentation, which is B4-C6 in this case. The final result is in A8 and some of its data are as follows:

In addition, an esProc program can be called by a reporting tool or a Java program in a way similar to that in which a Java program calls an ordinary database. The JDBC provided by esProc can be used to return a computed result of the form of ResultSet to the Java main program. For more details, please refer to the related documents.