EDO

Data Manipulation

Detailing the markups used to access and manipulate data resources.

• Introduction
• FOREACH
• IMPORT
• EXPORT
• DELETE
• ASSIGN
• CLEAR

Introduction

The <FOREACH> markup is similar to an SQL SELECT query (it isn't called <SELECT>, as there is already a standard HTML markup called that). It reads multiple records from a data source, and can do something for each record. The options available to it mostly correlate to normal SELECT parameters. These include:

RECORD (required)

This defines what record to use to store each row of data while processing it. If a record does not contain all the fields from the views/tables being queried, then those fields are ignored. Likewise, queried fields need to have names matching the record variables to be stored in the correct record field.

VIEW (required)

This defines what view (or views) to retrieve the data from. If more than one view is specified (comma-separated), then a relation/join between the views should be defined in the WHERE or JOIN options. Views can also be aliased to another name for use elsewhere in the query, using the syntax "viewname as viewalias".

FIELDS (optional)

This optional parameter lists (comma-separated) what fields to retrieve from the view(s), to store in the record. If this is undefined, then all matching field names will be copied. Fields with different names between the view and record can be renamed using the syntax "viewfield as recordfield". This syntax can also be used to include SQL function calls on the queried columns, storing the result in the right record field.

WHERE (optional)

This contains an optional condition to filter the data. Although this can also contain a condition for joining multiple views, that is preferred to be defined in the JOIN parameter.

JOIN (recommended for multiple views)

This is similar to WHERE, but should specifically contain the conditions defining the relationship between multiple views. This is useful, because for some advanced forms of <FOREACH>, it is necessary to treat the row filter separately to the view relationships. (See examples later on for more information.)

MATCH (optional)

This is an alternate condition-matching method. When one or more fields are listed (comma-delimited) in the MATCH parameter, the query only matches those rows whose values of these fields exactly match the values already stored in the target record.

SORT (optional)

Defines what field(s) to sort the queried rows by.

LIMIT (optional)

Similar to some SQL databases, this parameter allows the number of iterations to be limited to a certain number. (Currently only implemented for DBs which natively support the LIMIT SQL Select parameter.)

GROUP (optional)

Group rows together by a field name with the same values across rows. Used in SQL Queries for use with group-related field functions.

In addition to the <FOREACH> parameters, there are a number of custom markups which may be used within a Foreach block:

<PRELIST>

Any content within this markup is only shown before the main list of content. If there is no content (ie; no rows matching), then this is not shown at all.

<POSTLIST>

Any content within this markup is only shown after the main list of content. If there is no content (ie; no rows matching), then this is not shown at all.

<NOLIST> (Currently not implemented!)

Any content within this markup is only shown if there is no content (ie; no rows matching).

ie; To show a listing of multiple records, any HTML within a <PRELIST> comes before the list, and any HTML within a <POSTLIST> comes after the list, but only if that query returns at least one matching row. If there are no matches at all, then the HTML within the <NOLIST> will be shown.

Here's some examples of Foreach queries:

Selecting all fields from View "people" into Record "person", ordered by field "surname", displaying the "firstname" and "surname" in a list:

<FOREACH RECORD="person" VIEW="people" SORT="surname">
   $person.firstname $person.surname<BR>
</FOREACH>

Selecting fields "firstname" and "surname" into Record "person" from View "people", where the field "surname" matches the value already stored in the record:

<FOREACH RECORD="person" VIEW="people" MATCH="surname">
   $person.firstname $person.surname<BR>
</FOREACH>

Selecting all fields from View "people" into Record "person", ordered by field "surname", displaying the "firstname" and "surname" in a table, with 2 columns and appropriate headers, if there are matches:

<FOREACH RECORD="person" VIEW="people" SORT="surname">
 <PRELIST>
   <TABLE>
   <TR> <TH>First Name</TH> <TH>Surname</TH> </TR>
 </PRELIST>
   <TR> <TH>$person.firstname</TH> <TH>$person.surname</TH> </TR>
 <POSTLIST>
   </TABLE>
 </POSTLIST>
</FOREACH>

Selecting fields "firstname" and "surname" into Record "person" from Views "people" and "status" where field "current" in "status" equals "Y". The two views both contain a field "ID" and are joined on this field. The matching names are sorted by "surname", and listed in a bulleted list:

<FOREACH RECORD="person" VIEW="people,status" 
   FIELDS="firstname,surname" SORT="surname"
   WHERE="status.current='Y'" JOIN="people.ID=status.ID">
 <PRELIST>
   <UL>
 </PRELIST>
   <LI>$person.firstname $person.surname</LI>
 <POSTLIST>
   </UL>
 </POSTLIST>
</FOREACH>

The <IMPORT> markup is used to import data from a data source (specified by a View) or from another record. It is similar in many ways to the <FOREACH> markup, since it supports most of the same arguments. The primary differences are:

• IMPORT only reads one set of data into a specified record (rather than looping through multiple rows).
• IMPORT can also copy values from another record.
• IMPORT can retrieve data from special records and views, such as CGI.

Compared to <FOREACH>, <IMPORT> supports the following equivalent properties: RECORD, VIEW, FIELDS, WHERE, JOIN, MATCH, SORT.

The <IMPORT> markup also supports the following specific properties:

FROMRECORD

The name of the record to Import from. Used when copying multiple values from one record to another, as opposed to importing from another type of data source.

Here's some examples of Import queries:

Import all matching fields into Record "formdata" from the special view "CGI" (ie; populate "formdata" with data submitted as a CGI):

<IMPORT RECORD="formdata" VIEW="CGI">

Only import the fields "date", "title", and "notes" into Record "formdata" from the special view "CGI":

<IMPORT RECORD="formdata" FIELDS="date,title,notes" VIEW="CGI">

Import all matching fields into Record "formdata" from the record "olddata":

<IMPORT RECORD="formdata" FROMRECORD="olddata">

The <EXPORT> markup is used to export data from a Record, to a View (typically an SQL Table). It functionally fills the role of the SQL UPDATE and INSERT commands, determining which to use via the presence or absence of a condition-style parameter. ie; if a MATCH or WHERE parameter is specified (similar syntax as in <FOREACH>), then it updates existing matching records, otherwise it inserts a new record. it inserts a new record.

An optional FIELDS parameter can be set to export a selection of fields. If not set, the default behaviour is to export all fields that exist in both the record and the view.

If a condition (MATCH/WHERE) is specified, but the update fails, then an error message will be generated. If the desired behaviour is such that if no existing record is found, a new record is inserted, then the additional property ONERROR can be set to "insert". Warning: This behaviour is largely untested, and may change in the future!

Here's some examples of Export queries:

Export all fields from the Record "formdata" as a new row in View "data":

<EXPORT RECORD="formdata" VIEW="data">

Export fields "firstname" and "surname" from the Record "formdata" to the View "people", where the field "ID" matches the current value in the record "ID" field:

<EXPORT RECORD="formdata" VIEW="people" MATCH="ID">

The <DELETE> markup is used to delete data from a View. It uses fields from an optionally specified RECORD in conjunction with a MATCH or WHERE condition, to specify which rows of data in a View to delete. Is the RECORD really Optional?

Here's some examples of Delete statements:

Delete from View "data" all rows whose fields "group" and "section" match those stored in a Record "org":

<DELETE WHERE="group='$org.group' and section='$org.group'">

The same example, but using a defined RECORD and MATCH:

<DELETE RECORD="org" MATCH="group,section">

The <ASSIGN> markup is another way of getting information into Record Fields, but does it one Field at a time; it's a way of assigning a value to a single field. Parameters include:

RECORD (optional)

This defines which record the field is in to assign the value to. If this is not specified, the default Record is assumed. A record can also be specified in the FIELD parameter.

FIELD (required)

This defines which field is to be updated to the new value. If the RECORD parameter is not used, a record can still be specified as part of the Field specifier, using the notation: record.field

VALUE (required if EVAL is not specified)

This specifies the content of the value to assign to the field.

EVAL (required if VALUE is not specified)

This specifies an SQL expression to evaluate, with the resulting value being saved as the value to assign to the field. This currently requires a SOURCE to be specified, as that source is used to evaluate the expression. As such, built-in functions of the source can be leveraged for complex calculations.

SOURCE (required if EVAL is used)

The SQL source to use to evaluate an expression.

Here's some examples of Assign statements:

Assign to the field "count" the value of "0", in the default Record:

<ASSIGN FIELD="count" VALUE="0">

Assign to the field "count" the result of evaluating the expression "6*8", in the Record "track", using the Source "data":

<ASSIGN RECORD="track" FIELD="count" SOURCE="data" EVAL="6*8">

Increment the value of the field "count" by 1, in the Record "track", using the Source "data":

<ASSIGN FIELD="track.count" SOURCE="data" EVAL="$track.count.value+1">

($track.count.value means insert the "value" of field "count" from record "track")

The <CLEAR> markup is used to clear the values of one or more fields in a record. Parameters include:

RECORD (required)

This defines which record contains the fields to clear. Warning: This currently does not default to the default Record, and so is currently a required parameter.

FIELDS (optional)

This specifies which fields within the record to clear. This parameter is optional, and if not specified, all fields within the record will be cleared.