Table Of Contents

The OQL Language

The OQL service provider: riv_oql

OQL Prerequisites

Introduction to OQL

Prerequisites

`How to' Index

DATATYPE

DELETE

DROP

INSERTION

JOINS

OBJECT CREATION

The eval statement

QUERIES

SHUTDOWN

TABLE CONSTRAINTS

UPDATE

USING OQL

Grammar definitions

Other conventions used to describe the OQL syntax

Using OQL

Literal keywords of OQL

Sample databases

Sample database 1: the engineering database

Sample database 2: the ethernetSwitch database

Datatypes used in OQL

Names, numbers, strings and other things to note in OQL

Operators used in OQL

General syntax

Object nomenclature

OQL object definitions

Objects defined in OQL

Table constraints

External datatype declaration

Function Declarations

Evaluation declaration

Expressions

Tests for truth

Logical operators in OQL

Comparison tests

All or any predicate

Search conditions

Conjunctive operators in OQL

Manipulative statements in OQL

Queries

Joins

The eval statement

Conclusion

The OQL Language

---

This section describes the Object Query Language (OQL), which MWFM NMOS uses to transfer data between its databases, and the OQL service provider, riv_oql, which you can use to log into and interrogate databases.

The OQL service provider: riv_oql

The Object Query Language (OQL) is used by the MWFM NMOS processes to transfer data between the different application databases. It is used in the Stitchers to make up complex Stitching rules and it is possible for you to log into any MWFM application and interrogate the databases of the application. This is accomplished via the OQL service provider riv_oql, specifying the domain and service you wish to interrogate. You must specify a username, and riv_oql prompts you for a password, which you must enter correctly in order to log into the specified service. An example command line is given below.

---

Note riv_oql: The OQL service provider allows you to interrogate and even alter the MWFM NMOS databases.

---

riv_oql -domain <DOMAIN_NAME> -service <SERVICE_NAME> -username <USER_NAME> &

<DOMAIN_NAME>	Defined as the name of the domain where the service you wish to interrogate is running.
<SERVICE_NAME>	Refers to the name of the service you wish to interrogate; valid entries for the service are RivModel, RivEvents, RivMonitor, etc.
<USER_NAME>	Refers to the name of the user wishing to log into the service, the username must have an associated password, which you will be asked for and you must enter correctly before login can be completed.

OQL Prerequisites

In order to enable the successful interrogation of any of the service databases, the OQL service provider riv_oql requires that AUTH, the authentication engine, be running; this is necessary to ensure that the user who intends to interrogate and possibly alter the databases pertaining to the service has the necessary rights to do so.

Introduction to OQL

OQL is an object-based version of SQL designed specifically around the operational needs of the MWFM NMOS architecture.

The differences between OQL and traditional SQL are highlighted below:

1. OQL has the ability to support object referencing within tables. It is possible to have objects nested within objects.

2. Not all SQL keywords are supported within OQL; irrelevant keywords have been removed from the syntax.

3. OQL has the ability to perform mathematical computations from within OQL statements.

Prerequisites

---

Caution You are strongly advised to avail yourself of the information contained in "Language Prerequisites," before you proceed any further.

---

`How to' Index

The following section is a quick reference for common tasks in OQL; the locations of the relevant sections where the material can be viewed are enclosed in brackets.

DATATYPE

1. Acceptable datatypes in OQL.

2. How to define column datatypes for base tables.

3. How to declare external datatypes.

DELETE

1. How to delete records from a base table.

2. How to delete a record identified by an Object Partial.

3. How to delete a record identified by a List Whole.

4. How to delete a record identified by a List Partial.

DROP

1. How to drop a base table.

2. How to drop a database.

INSERTION

1. How to insert data into a table.

2. How to insert results of a query into a table.

JOINS

Simultaneously extracting data from different tables.

1. How to structure a join.

OBJECT CREATION

Creating a database or table.

1. How to create a database.

2. How to create a base table.

The eval statement

1. Functionality of the eval statement.

QUERIES

1. How to select data from a table.

2. Conditional selection of records.

3. How to select data into a table.

4. How to select data from multiple tables.

5. How to define a sub-query (see Q.5).

6. How to query a query (selection of data from the results of a query, also known as a sub-query). See Q.5, , , , .

SHUTDOWN

1. How to shut down the database.

TABLE CONSTRAINTS

1. How to apply a UNIQUE column constraint during base table creation.

2. How to apply a PRIMARY KEY column constraint during base table creation.

UPDATE

1. How to update/change specific records in a base table.

2. How to update an Object Whole.

3. How to update an Object partial.

4. How to update a list whole.

5. How to update a list partial.

USING OQL

1. EBNF syntax notation used to describe OQL syntax.

2. How to parse OQL statements.

Grammar definitions

The Extended Backus-Naur Form (EBNF) notation is used to describe the syntax used by OQL. The meta-level characters used in EBNF are shown in Table 33-1:

Table 33-1 The Extended Backus-Naur Form (EBNF) Meta-level characters

Character	Description	Usage
::=	Colon, Colon Equals	Marks the two halves of a syntax rule. It can be read as: the header appearing on the left-hand side of the symbol is defined by the text following on the right-hand side of the symbol.
|	The vertical bar character	Marks the start of an alternative definition.
[ ]	Square brackets	There may be zero or one item within each set of square brackets.
{ }	Curly brackets	There may be zero or more items within the braces.
< >	Angle brackets	A syntax rule.
n	Superscript number	Following a reference to a syntax rule, this denotes the number of the heading that describes the syntax rule. Following a keyword, this denotes where the explanation of the keyword is located.
' '	Single quotes	These denote punctuation characters that are part of the syntax.

Each numbered heading on the following pages describes a specific syntax rule. The language definitions for each component are shown following the header in highlighted blocks of text. Within each block of text, the keywords and punctuation for the language are shown in bold. The syntax rules are given in italics and surrounded by less-than and greater-than signs.

The references to the numbered headings are given in superscript following the references to the syntax rule. The following is an example syntax and is given to illustrate the conventions used in this section. The syntax contains four rules, <Example1>, <Example2>, <Example List>, <Example Identifier>.

<Example1> ::=

0 <Example Identifier> EXAMPLE ONE FIRST ALTERNATIVE <Example Identifier> ' ; '

0 | EXAMPLE ONE SECOND ALTERNATIVE [<Example2>] ' ; '

0

The first rule is called 'Example1'. It shows, using the vertical bar, that it has two alternative definitions.

The first definition denotes a phrase containing an <Example Identifier > followed by some specific keywords (EXAMPLE ONE FIRST ALTERNATIVE), followed by another <Example Identifier>, and terminated with a semi-colon (;).

The second definition denotes a phrase that contains some alternate keywords (EXAMPLE ONE SECOND ALTERNATIVE), followed by an optional <Example2> and again terminated with a semi-colon (;).

<Example2>::=

0 OPTIONAL EXAMPLE LIST '[ '<Example List> ' ] ' ' ; '

0

The second rule is called: 'Example2', and has only one definition. This definition denotes a phrase that contains some keywords (OPTIONAL EXAMPLE LIST) followed by an <Example List> that must be enclosed within square brackets.

<Example List>::=

0 <Example Identifier> { ' , '<Example Identifier> }

0

The next component of the grammar is called 'Example List'. This is denoted by an <Example Identifier> followed by zero or more <Example Identifier> parameters separated by commas (,).

<Example Identifier> 

The final component of the grammar is called <Example Identifier>. This relates to a terminal symbol without sub-components. Its definition contains a description in the text of what the terminal symbol consists of and a few examples.

<Example Identifier> contains any alphabetic or sequence of alphabetic characters. The following are examples of <Example Identifier>:

•Abcde xyz AaBbCcDdEe

Having defined the grammar, we can now give some examples. The following phrases are all grammatically correct, according to this example syntax:

•Fred EXAMPLE ONE FIRST ALTERNATIVE bloggs.

•EXAMPLE ONE SECOND ALTERNATIVE.

•EXAMPLE ONE SECOND ALTERNATIVE OPTIONAL EXAMPLE LIST [a, b, c, d, e].

•EXAMPLE ONE SECOND ALTERNATIVE OPTIONAL EXAMPLE LIST [A].

Other conventions used to describe the OQL syntax

The opt convention

In the course of describing some of the OQL syntax, an "opt" convention is used to describe optional elements of the syntax. This makes it easier to spot the essentials of any OQL syntax description. Optional elements within the syntax enclosed in angular brackets are preceded by the word "opt".

---

Note The conventions are designed to make it easier for you to recognise the keywords and their essential components in the syntax definitions.

---

An example syntax notation is described using the EBNF notation as shown below:

Line 1: <Example2>::= Line 2: EXAMPLE LIST '[' <Example List> ' ]' ';' Line 3: Line 4: <Example List>::= Line 5: [<list_element> ]

In the above syntax description, you are able to determine that the <Example_list> part of the syntax is optional only after reading the definition of the <Example_list> on Line 4.

Whereas the same example described using the `opt' convention is shown below:

Line 1: <Example2>::= Line 2: EXAMPLE LIST '[' <opt_Example List> ' ]' ';' Line 3: Line 4: <opt_Example List>::= Line 5: [<list_element> ]

With the aid of the `opt' convention, you are able to determine as early as Line 2 that the <opt_Example_list> part of the listing is optional and hence you have the choice of ignoring that part of the syntax from the beginning.

OQL keyword capitalization convention

The OQL keywords are capitalized and bolded for clarity and to enable you to easily distinguish them from other words and characters in the examples used to demonstrate their functionality. However, these words should not be capitalized when either using them as part of a schema or when using the OQL service provider riv_oql.

Using OQL

In order that all statements be parsed correctly, OQL requires that all input called an <oql_list> be structured as below.

<oql_list>::=

0 <oql> ; ' { <oql> ' ; ' }

0

According to the above rule, single OQL statements terminated by a semicolon, or a sequence of OQL statements separated by semicolons and terminated by a semicolon, are all valid for parsing.

<oql> ::=

0 <db_def> | <manipulative_statement>

0

The above defines an OQL statement as either a database definition or a manipulative statement.

When using the riv_oql OQL service provider, a list of OQL statements is executed using the send command and pressing the <enter> key as shown by the example given below:

DELETE FROM ethernetswitch.nodeByNeighbor WHERE ListData = [ "one", "two", "three" ]; send <enter> . ( 1 record(s) : Transaction complete )

---

Note The send command is not used when a database is implemented from within the code itself, e.g., from within a database schema configuration file, or from within a text Stitcher. Instead, the service being executed (e.g., riv_disco) carries out the OQL statements in the order specified by the code.

---

Literal keywords of OQL

The lists below contains all the keywords available in OQL and a brief description of each. The keywords are followed by superscripts that serve as references to the part of this document where the reader can find the syntax fully defined and contextual examples.

Table 33-2 List of implemented keywords in OQL 

Keyword	Description
ANDQ.4	Conjunctive operator used to combine many search conditions. All search conditions combined using this operator must be true in order for the condition to be passed.
ANY	Used within the context of the GROUP BY and HAVING keywords. It tests the output of a sub-query against a specified expression and displays the records that test TRUE against any of the conditions.
ALL	Used within the context of the GROUP BY and HAVING keywords. It tests the output of a sub-query against a specified expression and displays the records that test TRUE against all of the conditions.
BETWEEN	Logical operator that tests for truth between a specified range of values.
CHAR CHARACTER	Used to declare fixed length character datatypes.
COUNTER	Records the number of duplicate record insertions into the table.
CREATE,	Creates and defines database objects.
DATA	Declares a datatype designed for opaque (binary) data.
DATABASE	Used with the CREATE and DROP keywords to specify action upon a named database.
DEFAULT	Specifies a default value for a database column.
DELETE	Removes objects and data from the database.
DISTINCT	Used to select records that are distinct and ignore any duplicates from the results returned by a query.
DROP	Removes databases and database tables from the system.
EXISTS	Logical operator used to test for existence within a sub-query. Returns true or false.
EXTERNAL	Declares datatypes not defined in present schema.
FLOAT	Declares a decimal datatype.
FROM	In conjunction with SELECT, specifies the locations from which data is to be retrieved.
GROUP BY	Groups returned data into a sets based on specified columns.
HAVING	Used in conjunction with GROUP BY as a further data constraint through the introduction of search conditions.
IN	Logical operator used to test for the existence of left hand expression in right hand list or sub-query.
INSERT	Populates the database with records.
INT INTEGER	Declares Integer datatypes.
INTO	Used in conjunction with SELECT and INSERT to specify the object into which data is to be inserted.
IPADDRESS	Declares a datatype designed for IPaddresses.
IS	Introduces conditions such as NULL and NOT NULL.
LIKE	Logical operator used to test for similarity between values using UNIX regular expressions.
LIST	Declares a datatype designed for lists.
LONG	Declares a 32-bit numerical datatype.
LONG64	Declares a 64-bit numerical datatype.
NEXT	Allows you to refer to the next element in a sequential list data type.
NOT	Negates a condition.
NULL	Defined as an entity that has not been assigned a value. This is not the same as zero or white space.
OBJECT	Declares a datatype designed for objects.
ORQ.4	Conjunctive operator used to combine many search conditions. Only one of the search conditions combined with this operator needs to be true.
ORDER BY	Sorts the output of a query in either ascending or descending order.
PRIMARY KEY	Specifies the primary key constraint on a base table column; the primary key is used when combining data from two or more tables in a query.
SELECT	Keyword used to choose data from the database.
SET	Used with the UPDATE keyword to amend database records.
SHUTDOWN	Terminates the OQL service provider.
SMALLINT	Declares an 8-bit integer datatype to save memory.
TABLE	Specifies a table as the object of the preceding action.
TEXT	Declares a datatype designed to hold plain text (CHAR or VARCHAR).
TIME	Declares a datatype designed to hold time information.
TIMESTAMP	Column constraint used to hold time information.
TYPE	Forces conversion of datatype on right to datatype on left hand side.
UNIQUE	Used as a table constraint to ensure that no duplicate entries are accepted into the specified columns.
UPDATE	Amends the details of existing data in a database object.
VALUES	Used in conjunction with INSERT and UPDATE to specify the values to be amended or inserted into a table.
VARCHAR	Declares variable length character datatypes.
VOLATILE	A table constraint which specifies that the contents of the table should be held only in memory and not committed to disk.
WHERE	Introduces criteria and conditions to identify particular records.

Sample databases

In order to enable you to fully understand the syntax of the keywords and appreciate the full functionality of OQL, contextual illustrations of the keywords using sample databases where appropriate. In the examples which describe the OQL syntax, the OQL service provider riv_oql has been used to log into the sample databases and manipulate the data using the demonstrated keyword.

The sample databases are fully described below with examples showing how the database objects were created and populated with data. In order to describe some more advanced features not supported by traditional SQL, a database with the more complex datatypes is used for illustration purposes. This sample database is also described and defined in the following sections

Figure 33-1 .Chart showing structure and components of the sample databases

Sample database 1: the engineering database

For the purpose of explaining the simple operations of some OQL keywords, a test database called engineering is used. The tables and their contents are shown below

.

Table 33-3 Table showing members of the development group 

engineering.dev
EmpID	Name	Job	Language	Sex	Age
R001	James	Prog	C	M	21
R002	John	Prog	C++	M	23
R003	Simon	Research	Java	M	22
R004	Chris	Manager		M	27
R005	Lee	Testing		F	20
R006	Jenny	Debugging	HTML	F	22
R007	Phillip	Testing		M	23

Table 33-4 Table showing members of the testing group

engineering.test
EmpID	Name	Job	Sex	Age
T001	James	Tech.auth	M	25
T002	Lucy	Quality	F	22
T003	Chris	Testing	M	28
T004	Carlos	Testing	F	28
T005	Thomas	Quality	M	24
T006	Elaine	Network	F	25
T007	Melanie	Planning	F	29
T008	Victor	Manager	M	22

Table 33-5 Table showing members of the documentation group

engineering.docu
EmpID	Name	Job	Sex	Age
D001	Lucy	Designer	F	22
D002	Edward	Editor	M	23
D003	Jennie	Tech.auth	F	19
D004	Murielle	Tech.auth	F	22
D005	Mariah	Tech.services	F	26
D006	Adebayo	Prog	M	25
D007	Kazeem	Manager	M	27
D008	Sting	Tech.auth	M	25

STEP 1: Creation of the database

The engineering database is created using the CREATE keyword. A detailed explanation and description of the syntax of database creation is given further on in this chapter. The following example shows the creation of the engineering database:

CREATE DATABASE engineering;

STEP 2: Creation of the tables

The database tables are created using the CREATE keyword. A detailed explanation and description of the syntax of table creation is given further on in this chapter. The following examples show how the sample database tables were created:

•Creation of the engineering.dev table

CREATE TABLE engineering.dev ( EmpID VARCHAR(4) NOT NULL PRIMARY KEY, Name CHAR NOT NULL, Job CHAR NOT NULL, Language CHAR, Sex VARCHAR(1) NOT NULL, Age SMALLINT NOT NULL, UNIQUE(EmpID) );

•Creation of the engineering.docu table

CREATE TABLE engineering.docu ( EmpID VARCHAR(4) NOT NULL PRIMARY KEY, Name CHAR NOT NULL, Job CHAR NOT NULL, Sex VARCHAR(1) NOT NULL, Age SMALLINT NOT NULL, UNIQUE(EmpID) );

•Creation of the engineering.test table

CREATE TABLE engineering.test ( EmpID VARCHAR(4) NOT NULL PRIMARY KEY, Name CHAR NOT NULL, Job CHAR NOT NULL, Sex VARCHAR(1) NOT NULL, Age SMALLINT NOT NULL, UNIQUE(EmpID) );

---

Note The NOT NULL constraint must come before the PRIMARY KEY.

---

Population of the tables with data

The next step after creation of the database and the database tables is the insertion of data into the tables. The tables are populated with data by using the INSERT statement. Examples of the variations of the insert statement used to insert data into the tables are shown below:

INSERT INTO engineering.dev VALUES ("r001", "James", "Prog", "C", "Male", 21 );

INSERT INTO engineering.dev (EmpID, Name, Job, Language, Sex, Age ) VALUES ("r002", "John", "Prog", "C++", "M", 23);

INSERT INTO engineering.dev (Age, Sex, Name, Job, EmpID, Language) VALUES ( 22, "M", "Simon", "Research", "r003", "Java");

INSERT INTO engineering.dev VALUES ( "r004", "Chris", "Manager", "", "M", 27);

---

Note The INSERT INTO keywords must be followed by the VALUES keyword, else an OQL error is generated.

---

Sample database 2: the ethernetSwitch database

To illustrate the more complex features of OQL, a database called `ethernetSwitch' that uses some of the more sophisticated datatypes is used. Its component tables are shown in Figure 33-1. As an example, the focus will be on the nodeByNeighbor table, which has the following structure:

Table 33-6 The nodeByNeighbor table

Node name	Neighbor name	Net address	Object data	List data
sminky	sminky.1	195.255.200.192	{ ifIndex=20, IfDescr="utp10/100"}	["one", "two", "three" ]

STEP 1: Creation of the database

As is the case for the engineering sample database, the ethernetSwitch database is created as shown below:

CREATE DATABASE ethernetSwitch;

STEP 2: Creation of the tables

Creation of the ethernetSwitch.nodeByNeighbor table:

CREATE TABLE ethernetSwitch.nodeByNeighbor ( NodeName VARCHAR(64), NeighborName VARCHAR(64), NetAddress IPADDRESS, // demonstrates use of IPADDRESS // datatype ObjectData OBJECT TYPE arthur, //demonstrates use of // OBJECT datatype ListData LIST TYPE TEXT // demonstrates use of LIST // datatype );

STEP 3: Inserting data into the table

Similar to the engineering databases, the next step after creation of the database and the database table is the insertion of data into the table. The table is populated with data by using the INSERT INTO statement as shown below:

INSERT INTO ethernetSwitch.nodeByNeighbor (NodeName, NeighborName, NetAddress, ObjectData, ListData) VALUES ( "sminky", "sminky.1", "195.255.200.192", {ifIndex=20, ifDescr="utp10/100"}, ["one", "two", "three"] );

Datatypes used in OQL

Table 33-7 lists all the allowable datatypes in OQL syntax.

Table 33-7 Table of datatypes used in OQL 

Datatype category	Datatype syntax	Datatype description	Example return values in the OQL syntax
Character	CHAR [ACTER]	Stores fixed length characters.	IDENT TEXT IDENT TEXT Note Extra characters beyond the specified VARCHAR length are truncated. INTNUM APPROXNUM
	VARCHAR ' (' n ') '	Stores variable length characters. Length specified by positive integer n.
Numerical	INT [EGER]	Stores integer values.
	INT [EGER] TYPE <data_type>	Stores the conversion of declared data-type to integer data-type.
	SMALLINT	Stores an 8-bit integer. Used to save memory.
	FLOAT	Stores decimal values.
	LONG	Holds a 32-bit numerical value.
	LONGLONG	Holds a 64-bit numerical value.
Special	IPADDRESS	Specially designed for IP addresses.
	DATA	Holds opaque data, typically of the binary format.
Time	TIME	Holds information pertaining to time.
List	LIST TYPE <data_type>	Holds a list of particular data-types.	<list>
Array	ARRAY TYPE <data_type>	Holds an array of a specified data-type.	<array>
Object	OBJECT TYPE <data_type>	Holds objects of particular data-types.	<object>

Names, numbers, strings and other things to note in OQL

NAMES

Rules for NAMES: return datatype = IDENT

1. Names must start with a letter of any case.

2. Names may be a combination of one or more characters consisting of numbers and letters of any case.

Examples are usernames, database object names (databases, database tables, columns) and variable names.

NUMBERS

Rules for NUMBERS and the various return types.

1. Numbers can be integers but must be positive. Return type=INTNUM.

2. Numbers can be decimal of any precision In this case 1.2322, 0.343, 121.454545454, .023, .12, .1 are all valid numbers. Return type= APPROXNUM.

3. Numbers can be expressed in an exponential format; examples of this are 23 E32, 2e9, 34E +11, 2.33e -4, 2.44E+21, .3e-3. Return type = APPROXNUM.

STRINGS

1. A string is any text enclosed in quotation marks; any of " TEXT " or 'TEXT'. "list of spec", 'database' , "schema" are valid strings. The string datatype is also used when evaluating the eval statement, the eval statement requiring either a textual string beginning with a dollar symbol ($) or a string of characters beginning with an ampersand character (&). Return type= STRING.

2. A string with missing or unmatched terminating quotes will return the `un-terminated string' error. " Database, ' the list ", "excellent ' are not valid strings.

OTHER 

1. When the <enter> key is pressed during the use of the riv_oql OQL service provider, the OQL line counter is incremented by 1.

2. Comments are indicated by the sequence "- -" or "/ / " . If a comment is so long that it requires a carriage return, the characters on the next line must also be commented out

.

Table 33-8 Punctuation used in the OQL syntax 

Symbol	Meaning
-	Subtraction, negative
+	Addition, positive
*	Multiplication, wild card Note The wild card matches any characters within its expression.
/	Forward slash
(	Open bracket
)	Close bracket
,	Comma
.	Period
;	Semicolon (For statement termination)
~	Tilde (For regular expression matching)
->	Target identifier (Retrieves a sub value of an object)

Operators used in OQL

Precedence and associativity

Precedence and associativity determine the grouping of operators with operands. Precedence determine the operators in an expression that are executed first, e.g., the expression a + b * c is ambiguous in the sense that it could either be ((a + b) * c) or (a + (b * c)).

Mathematical and programming practices specify that multiplication and division take precedence over addition and subtraction, however, the opposite is the case for OQL. Hence, the given example a + b * c means (a + b) * c in OQL practice not a + (b * c) as would be the case for standard practice.

Operators with higher precedence are executed before operators of lower precedence, though the order may be broken at any time with the use of parentheses. In the above example, if it were desirable to perform multiplication before addition, the expression would take the following form: a + (b x c).

Associativity controls the grouping of operators at the same precedence level. Operators may be left-associative, right-associative or non-associative. For example, in the ambiguous expression: a + b - c, using left-associativity would imply (a + b) - c while right-associativity would imply: a + (b - c).

In OQL, all operators except the minus sign (-) are left associative. A minus sign implying a negative value is non-associative.

.

Table 33-9 OQL operators in order of descending precedence 

Operator	Description	Associativity	Precedence
-		Non-associative	1 (Highest)
OR	Logical OR	Left	2
AND	Logical AND	Left	3
NOT	Logical NOT	Left	4
= <> < > <= >=	Equality Not equal to Less than Greater than Less than or equal to Greater than or equal to	Left	5
+ -	Addition Subtraction	Left	6
* /	Multiplication Division	Left	7 (Lowest)

Table 33-10 Arithmetic operators in OQL 

Symbol	Description