Although the present manual is not intended as an SQL tutorial, it has been considered necessary to present the supported SQL commands, together with a brief description of the commands, to inform the user about existing commands and their use. The command syntax, as well as a short example, where necessary, are also provided.

Most of the commands supported by Ovrimos are standard SQL commands. There are a few, however, such as CREATE USER, etc. that are SQL-like, but do not belong to the "body" of SQL.

SQL Commands

Alter Table

Modifies the description of an existing table.

Call

Invokes a stored procedure.

Create Domain

Defines a domain, i.e. a user defined type with some constraints.

Create Index

Defines a new index on an existing table.

Create Table

Creates a new database table.

Create User

Creates a new database user.

Create View

Defines a view, i.e. a virtual table.

Delete Positioned

Deletes the row where the cursor is positioned.

Delete Searched

Deletes the row (or rows) that conform to certain query predicates.

Drop Domain

Removes a previously defined type.

Drop Index

Removes a previously defined table index.

Drop Table

Removes a previously defined table, deleting all its data.

Drop User

Removes a previously defined database user.

Drop View

Removes a database view.

Grant

Grants privileges on a table or view.

Insert

Inserts one or more rows into a table.

Revoke

Revokes privileges from a table or view.

Select

Returns the results of a query in one or more tables or views.

Select for Update

Prepares the results of a query in one or more tables or views for update.

Set Time Zone

Defines the local time zone.

Set Transaction

Sets the attributes of the next transaction (isolation level etc.).

Update Positioned

Updates the values of the row where the cursor is positioned.

Update Searched

Updates the row or rows that conform to certain query predicates.

Update User

Updates the attributes of a database user.

Syntax and examples of supported SQL commands

To present the SQL commands and the effect these commands have on a database, we assume that there is a simple application where two tables will exist, one with planets and their data and the other with satellites and their data.

These tables will contain at a certain point the following data:

                        PLANETS
 
 

planet_id	planet_name	description	planet_image	distance
1	Mercury	The hot planet	BLOB
2	Venus	The greenhouse planet	BLOB
3	Earth	Mostly harmless	BLOB
4	Mars	The red planet	BLOB
5	Jupiter	The giant planet	BLOB
6	Saturn	The ringed planet	BLOB
7	Uranus	The blue planet	BLOB
8	Neptune	The next to last planet	BLOB
9	Pluto	The cold planet	BLOB

The table planets has a unique key planet_id.

              SATELLITES
 
 

sat_id	sat_name	planet_id	sat_image
1	Luna	3	BLOB
2	Phobos	4	BLOB
3	Deimos	4	BLOB
4	Io	5	BLOB
5	Europa	5	BLOB
6	Ganymede	5	BLOB
7	Callisto	5	BLOB
8	Rhea	6	BLOB
9	Dione	6	BLOB
10	Titan	6	BLOB
11	Mimas	6	BLOB
12	Miranda	7	BLOB
13	Triton	8	BLOB
14	Charon	9	BLOB

The table satellites has a unique key sat_id and is connected to table planets via planet_id.

Later in this section, we will show a way to insert and retrieve BLOBs.

At some point, a user pandora and a user friend are added to the system.
Pandora user has the same privileges as admin.

Alter Table
The Alter Table command is applied to modify the definition of an existing table.

ALTER TABLE table name
    ADD [COLUMN] column definition ;

Example:

ALTER TABLE planets
  ADD (distance real);

The command modifies table planets, adding a new column of real numbers, called distance.
So, if the table was:
 
 

planet_id

planet_name

Description

planet_image

The altered table will be:
 
 

planet_id

planet_name

Description

planet_image

distance

Call
The Call is used to invoke a stored procedure,

CALL proc ...

Example:

CALL create_table;

where create_table is a stored procedure (in any language) which creates a table.

Create Domain
The Create Domain command is applied to define a new domain, i.e. a new data type.

CREATE DOMAIN domain name [ AS ] data type
   [ constraint definition ... ] ;

where
constraint definition ::=
check constraint

Example:

CREATE DOMAIN planet_order integer check value 0;

The newly created domain range will be used in the data definition as an integer number greater than 0, i.e. as a natural number.

Create Index
The Create Index command defines a new index on an existing table.

CREATE [UNIQUE] INDEX index name ON TABLE table name (column name [ASC|DESC].,..);

Example:

CREATE UNIQUE INDEX ind1 ON TABLE planets (planet_id ASC);

A new index, planet_id, will be used as an index in ascending order. Its values are unique.

Note: This is not a standard SQL command.

Create Table
The Create Table command creates a new table. It may be permanent or temporary.

CREATE TABLE table name
( {column definition | [table constraint] }.,..);

where
column definition ::=
column name { domain name | data type [size] }
[column constraint ...]
[DEFAULT default value ]

Example:

CREATE TABLE planets (
  planet_id planet_order;
  planet_name varchar(20);
  description varchar (100);
  planet_image long varbinary;
);

The newly created table planets will be:
 
 

planet_id

planet_name

description

planet_image

CREATE TABLE satellites (
  sat_id integer;
  sat_name varchar(20);
  planet_id integer references planets(planet_id);
  sat_image long varbinary;
)

The newly created table satellites will be:
 
 

sat_id

sat_name

planet_id

sat_image

The two tables are related via the planet_id column.

Create User
The Create User command defines a new database user. It is possible to use a LIKE clause that creates a user with the privileges of a previously defined prototype user, thus allowing privilege inheritance.

CREATE USER user name FOR 'full user-name' WITH 'password'|NULL [LIKE prototype user] ;

Example:

CREATE USER pandora FOR 'Mary Adams' WITH 'pass1'
LIKE admin;

This command will create a user with name pandora with initial password pass1. This new user is like admin, which means that she has full privileges. This is an example of privilege inheritance.

CREATE USER friend FOR 'Joe Phillips' WITH 'pass2';

This user has no admin privileges.

Note: This is not a standard SQL command.

Create View
The Create View command defines a view, i.e. a virtual table on one or more tables or views.

CREATE VIEW table name [(column list)]
  AS (SELECT statement);

All views created have as default CASCADED CHECK OPTION.

Example:

CREATE VIEW planet_list
AS (SELECT planet_id, planet_name FROM PLANETS);

The Create View command will make a view called planet_list with only two columns visible. For the users of this view the virtual table planet_list will appear like:
 
 
 

planet_id

planet_name

 

Delete Positioned
The positioned Delete command is used to remove a row from a table. The row is where the cursor has been previously positioned.

DELETE FROM table name
  WHERE CURRENT OF cursor name;

Example:

DELETE FROM planets
  WHERE CURRENT OF Cur1;

The cursor cur1 is previously defined to traverse the table. The cursor is positioned to a certain row usually after the execution of a select statement. The command will delete the row where the cursor is positioned.

Delete Searched
The searched Delete command is used to remove rows from a table. If a predicate exists, then the searched rows are deleted, otherwise the entire table contents are removed.

DELETE FROM table name
   [ WHERE predicate ];

Example:

DELETE FROM planets
  WHERE id > 8;

This command will delete the rows (if any) where the planet_id is greater than 8, i,e, Pluto will be removed from the table.

Drop Domain
The Drop Domain command is used to discard a previously created domain.

DROP DOMAIN domain name;

Example:

DROP DOMAIN planet_order;

After the Drop Domain command, the identifier range can no longer be used as a data type. The columns of tables defined by this domain are not affected from the domain deletion, since the domain is used as a macro which is expanded at the time of column definition and does not appear anywhere else.

Drop Index
The Drop Index command is used to discard a previously created index.

DROP INDEX index name;

Example:

DROP INDEX ind1;

After the Drop Index command, the columns involved in the ind1 index (i.e. planet_id) are no longer used as key. The requirement to use this index as a unique key is also no longer required.

Note: This is not a standard SQL command.

Drop Table
The Drop Table command is used to discard a previously created table.

DROP TABLE table name CASCADE | RESTRICT;

Example:

DROP TABLE Satelites;

The table satellites is removed and all its data are deleted.

Drop User
The Drop User command is used to remove a database user.

DROP USER user name;

Example:

DROP USER friend;

After the command, user friend is no longer in the system. All tables and views created by user friend are now visible only by the user admin, who may decide to grant privileges to some other user to manager these tables.

Note: This is not a standard SQL command.

Drop View
The Drop View command is used to discard a previously defined view.

DROP VIEW view name CASCADE | RESTRICT;

Example:

DROP VIEW planet_list;

View planet_list is removed from the database.

Grant
The Grant command is used to give privileges to users.

GRANT privilege.,.. ON object name
   TO { grantee.,..} | PUBLIC
   [ WITH GRANT OPTION ];

privilege ::=
  { ALL PRIVILEGES }
  | { SELECT
       | DELETE
       | { INSERT [ (column name .,.. ) ] }
       | { UPDATE [ (column name.,..) ] }
       | { REFERENCES [ (column name.,..) ]}}

object name ::=
  [ TABLE ] table name
  | DOMAIN domain name

Example:

GRANT ALL PRIVILEGES ON planets TO friend;
GRANT SELECT, INSERT ON satellites TO friend with GRANT option;

The first command allows full privileges to user friend on table planets.
The second allows SELECT and INSERT to user friend on table satellites. The grant option allows the user to pass this privilege to other users as well.

Insert
The Insert command is used to insert rows into a table.

INSERT INTO table name
  [  (column name.,..) ]
  query expression | table value constructor;

Example:

INSERT INTO PLANETS (planet_id, planet_name, description) values
(1, 'Mercury', 'The hot planet');
 

Revoke
The Revoke command is used to deprive a user from previously granted privileges.

REVOKE [ GRANT OPTION FOR ]
   { ALL PRIVILEGES } | { privilege.,..}
   ON object
   FROM PUBLIC | {grantee.,..}
   CASCADE | RESTRICT;

Example:
REVOKE INSERT, DELETE ON planets FROM friend;

REVOKE GRANT OPTION FOR SELECT ON satellites FROM FRIEND;

The first command revokes the INSERT and DELETE option from user friend, while the second disallows user FRIEND to grant the SELECT option to other users. (User friend retains his/her own privilege to SELECT from satellites).

Select
The Select command is used to return rows from one or more tables.

SELECT [DISTINCT]
  { { aggregate function.. | value expression [AS column name]}.,..}
  | {qualifier.*}
  | *
FROM { { table name [AS] correlation name] [ (column name.,..) ]}
  | { subquery [AS] correlation name [column name.,..]}
  |  joined table }.,..
[WHERE predicate]
GROUP BY {{ [table name | correlation name}.] column name}.,..
[HAVING predicate]
[  UNION   [ALL]
    select statement | {TABLE table name} | table value constructor ]
[ORDER BY {{ output column [ASC | DESC]}.,..}
   | {{positive integer [ASC | DESC]}.,..};

Example:

SELECT planet_id from planets where planet_name = 'Earth';
 
 

planet_id

3

SELECT count(*) from planets where distance > 15000000;
 
 
 

count

6

SELECT planet_name from planets where planet_id = (select planet_id from
satellites where sat_name = 'Miranda');

A Select with a subquery, where the two tables are connected via the planet_id field.
 
 

planet_name

Uranus

SELECT planet_name from planets ORDER by distance;
 
 
 

planet_name

Mercury

Venus

Earth

Mars

Jupiter

Saturn

Uranus

Neptune

Pluto

The planets will be returned, ordered by distance

SELECT planet_name, sat_name FROM planets, satellites
WHERE planets.planet_id = satellites.planet_id and planets.planet_id < 9;
 
 

planet_name	sat_name
Earth	Luna
Mars	Phobos
Mars	Deimos
Jupiter	Io
Jupiter	Europa
Jupiter	Ganymede
Jupiter	Callisto
Saturn	Rhea
Saturn	Dione
Saturn	Titan
Saturn	Mimas
Uranus	Miranda
Neptune	Triton

This last Select command joins the two tables and produces a sequence of rows with all the planets that have satellites and planet_id less than 9. Pluto is therefore excluded.

A few more Select examples follow, which have nothing to do with the planets, satellites tables, but are good examples indicating how complex queries may be written in SQL.

In the following examples, tables SALESPEOPLE, ORDERS, CITIES and CUSTOMERS are involved.

Select within a select
SELECT *
FROM ORDERS
WHERE SNUM =
(SELECT SNUM
FROM SALESPEOPLE
WHERE CITY = 'Barcelona');

Select with implicit table join
SELECT Customers.cname, Salespeople.sname,
Salespeople.city
FROM Salespeople, Customers
WHERE Salespeople.city = Customers.city;

SELECT Customers.cname, Salespeople.sname
FROM Customers, Salespeople
WHERE Salespeople.snum = Customers.snum;

Select with ANY
SELECT *
FROM SALESPEOPLE
WHERE CITY = ANY
(SELECT CITY
FROM CUSTOMERS);

Select with HAVING and MAX
SELECT ODATE, SUM (AMT)
FROM ORDERS A
GROUP BY ODATE
HAVING SUM (AMT) >
(SELECT 2000.00 + MAX (AMT)
FROM ORDERS B
WHERE A.ODATE = B.ODATE);

Select with Union
SELECT snum, sname
FROM Salespeople
WHERE city = 'London'

UNION

Select for Update
The Select for Update command selects rows of one or more tables in order to perform an update to the values of the rows returned.

select command FOR UPDATE OF;

where select command::=
SELECT [DISTINCT]
  { { aggregate function.. | value expression [AS column name]}.,..}
  | {qualifier.*}
  | *
FROM { { table name [AS] correlation name] [ (column name.,..) ]}
  | { subquery [AS] correlation name [column name.,..]}}.,..
[WHERE predicate]
GROUP BY {{ [table name | correlation name}.] column name}.,..
[HAVING predicate]
[  UNION  [ALL]
   select statement | {TABLE table name} | table value constructor ]
[ORDER BY {{ output column [ASC | DESC]}.,..}
   | {{positive integer [ASC | DESC]}.,..};

Example:

SELECT distance FROM planets WHERE planet_id = 4 FOR UPDATE OF;

The field distance may now be updated.

Set Time Zone
The Set Time Zone command defines a local time zone displacement.

SET TIME ZONE value | LOCAL;

Example:

SET TIME ZONE LOCAL;

The above command sets the time zone to the local time zone of the server.

SET TIME ZONE '+02:00';

The above command sets the time zone to +2 hours of GMT.
Times are always kept in GMT but they are retrieved as local times (according to the time zone).

Example of time zone usage. Suppose a table entrance of people entering a place and a timestamp held on their entrance:

               Entrance
 

Person_Name

Entrance time

set time zone '+02:00';
insert into entrance values ('John Smith', '11:05:00');

This is local time 11:05:00, which corresponds to GMT 09:05:00.

select * from entrance where person_name = 'John Smith';
 
 

John Smith

11:05:00

set time zone '+10:00';

select * from entrance where person_name = 'John Smith';
 
 

John Smith

19:05:00

Of course, the internal time is not affected by any of the set time zone commands. Internally, the time is GMT, i.e. the entrance time for John Smith is 09:05:00.

Set Transaction
The Set Transaction command sets the attributes of the next transaction, i.e. the isolation level, option to read or write into the database etc.

SET TRANSACTION { ISOLATION LEVEL
  { READ UNCOMMITTED
   | READ COMMITTED
   | REPEATABLE READ
   | SERIALIZABLE }
 | { READ ONLY | READ WRITE};

Example:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

The next transaction will be performed on a read committed level. See also The Lock Manager.

Update Positioned
The positioned Update command is used to update the contents of the row where the cursor has been previously positioned.

UPDATE table name
   SET { column name = { value expression | NULL }}
   WHERE CURRENT OF cursor name;

Example:

UPDATE planets SET distance = 210000000 WHERE CURRENT OF cur1;

The distance field of the row where cursor cur1 has been placed will be updated.

Update Searched
The searched Update command is used to update the contents of rows searched by a defined predicate. If the predicate is used, then only the rows confronting to the predicate are updated, otherwise all rows of a table are modified.

UPDATE table name
   SET { column name = { value expression | NULL }}
   { WHERE predicate };

Example:

UPDATE planets SET distance = 210000000 where planet_id = 4;

Update User
The Update User command modifies a database user's password.

UPDATE USER user name { WITH 'new password' | NULL }[LIKE prototype] ;

Example:

UPDATE USER pandora WITH 'xyzzy';

UPDATE USER friend LIKE pandora;

Note: This is not a standard SQL command.

Commit
The Commit command terminates a transaction, making the current changes permanent.

COMMIT [WORK];

Rollback
The Rollback command rolls backs to the beginning of the current transaction, undoing in the process all uncommitted changes.

ROLLBACK [WORK];
 

SQL FUNCTIONS

The functions described below are Standard SQL-2 functions. There is another set of supplemental functions, implemented to allow the users as much flexibility as possible from both  the SQL Terminal and sqlapp4 command line.

CAST

Converts a scalar value to a specified scalar data type. The syntax is:

The value of the specified scalar expression is converted to "data type".
Of course some data types may not be converted to others.
 

Returns one of the specified sets of values, depending on some conditions. The general format is as follows:

CASE
   when-clause-list
   ELSE scalar-expression
END
where a 'when-clause' takes the form:
  WHEN conditional-expression THEN scalar-expression

Example:

CASE
    WHEN value < 100 THEN 'Cheap'
    WHEN value < 200 THEN 'Medium'
    ELSE  'Expensive'
END
 

Returns the position within a specified character string (string2) of another specified character string (string1). Each of string1 and string2 is specified as an arbitrary character string expression. More precisely, the expression POSITION (string2 IN string1) is defined to return a value as follows:

If the CHARACTER_LENGTH of string2 is zero, the result is 1.
Otherwise, if string2 occurs as a substring within string1, the result is one greater than the number of characters in string1 that precede the first such occurrence.
Otherwise the result is zero.

Example:
POSITION ('morning', 'Good Morning')
returns 6.

Position does not apply to bit strings.

Extracts a substring of a string. For example, the expression
SUBSTRING ('Good Morning' FROM 3 FOR 4)
extracts  'od M'.
In general, the string argument can be any character or bit string expression, and the FROM and FOR arguments can be any numeric expression. If FOR is ommited, all the right part from FROM is extracted.

Returns a character string that is identical to a specified character string leading and/or trailing pad characters are removed.

TRIM (ltb pad FROM string)
ltb = leading, trailing or both. If omitted, option both is assumed.

pad is a character string expression that evaluates to a single character. The pad character to be removed is specified. If the pad character is not specified, then space  is assumed.
If ltb and pad are omitted, FROM must also be omitted.

USER

Returns the user name.

Example:

select user from tempo, returns the username.

NULLIF (expression1, expression2 )

 

The function relates two values. If they are the same, the condition is true and NULLIF returns the value NULL. Otherwise, the first of the two values are returned.

The expression NULLIF (x, y) is defined to be equivalent to the expression

CASE WHEN x = y THEN NULL ELSE x END

Example:

select nullif (2+3, 4+1) from tempo, returns null, while

select nullif (4+2, 4+1) from tempo, returns 6.

 

COALESCE(expression1, expression2, ...)

 

Function COALESCE receives a series of values. It traverses the list until it finds the first one that is not NULL. It then assumes the value. If no value other than NULL is found, then COALESCE assumes the NULL value.

 

The expression COALESCE (x, y) is defined to be equivalent to the expression

CASE WHEN x is NOT NULL THEN x ELSE y END

More generally, COALESCE (x,y, ..., z) returns null only if its operands all evaluate to NULL.

BLOBS and the URI function

Whenever a BLOB needs to be used, it is defined as either long varbinary or long varchar.  In the above example of planets and satellites, the image of the planet is defined as :

planet_image long varbinary

To insert into the table values for the BLOB the idea is to use SQL parameters (the ? symbol). Note that the SQL Terminal does not accept parameters, so BLOBS can only be defined by the sqlapp4 application.

For example, if the associated BLOB is a GIF file called earth.gif, then the insertion of the value into the column image is done with the following command:

insert into planets (image) values (? 'image/gif');

Here the ? is the parameter and 'image/gif ' is the mime type image/gif. As soon as this command is typed, the program responds with the prompt:

File to send for BLOB parameter 0?

At this point the user types the name of the GIF file, e.g. c:\nasa\earth.gif.

The file must be typed with the full path name as the operating system requires it for finding the file. No quotes are used for the file name.

Contrary to the insertion of the BLOB from the sqlapp4 program, it is not possible to retrieve a BLOB image from this application. An appropriate browser is required. The BLOB address, however, may be retrieved through the use of a special function, called URI.

URI stands for Uniform Resource Identifier and is used to return the BLOB address as a URL address. For example, the command:

Select planet_name, uri(planet_image) from planets where planet_id = 3;

in the sqlapp4 application program will respond with an output of the following form:

Planet_name |
------------------------------------------------------------
'Earth'     | http//128.134.23.140:8181/blobxxxxxxxx

The http address of the BLOB may be used by the HTTP server to display the BLOB data. See also the URI function.
 

Supplemental SQL Functions

Besides implementing the basic SQL commands, a wide variety of functions to be used together with SQL Commands is also provided. These functions belong to the following general categories: Mathematical functions, Date and Time functions, String manipulation functions, Blob manipulation functions and System functions.

A brief description of the functions, together with the arguments and the return values is provided below. Several functions are overloaded. The user may also note the underscore (_) in the beginning of some function names. This was necessary to
differentiate the function from SQL reserved words.

Before providing the functions, a short explanation of the arguments used is also necessary:

Expressions can be the names of columns, where this data type is defined. They may also be results of other scalar functions. It is also possible to be literals of the appropriate data type. The underlying data types for the following expressions are those presented below:

• numeric_expression

NUMERIC
DECIMAL
TINYINT
SMALLINT
INTEGER
BIGINT
FLOAT
REAL
DOUBLE
 
• float_expression

FLOAT
 
• integer_expression

TINYINT
SMALLINT
INTEGER
BIGINT
 
• string_expression

ascii_expression
unicode_expression
binary_expression
 
• ascii_expression

CHAR
VARCHAR
 
• unicode_expression

UNICODE_CHAR
UNICODE_VARCHAR
 
• binary_expression

BINARY
VARBINARY
 
• long_expression

LONG
LONGVARBINARY
LONGVARCHAR
 
• date_expression

DATE
TIMESTAMP
 
• time_expression

TIME
TIMESTAMP
 
• timestamp_expression

TIMESTAMP

Thus, the format for DATE is yyyy-mm-dd, and whenever used in an SQL statement, it must be enclosed in single quotes (') and preceded by the reserved word DATE. Thus, the 2nd of December of the year 1962 is described as:

DATE '1962-12-02'. Legal times range from '0001-01-01' to '9999-12-31'.

The format of TIME is hh:mm:ss or hh:mm:ss.ttt (with the .ttt part, signifying thousandths of a second optional). Whenever used in an SQL statement, it must be enclosed in single quotes (') and preceded by the reserved word TIME. Thus, 9.30 AM is described as: TIME '09:30:00' or '09:30:00.000'. Legal times range from '00:00:00.000' to '23:59:59.999'.

The timestamp is the date and time combined, with an optional time zone interval in the form of +hh:mm. Whenever used in an SQL statement, the timestamp expression must be enclosed in single quotes (') and preceded by the reserved word TIMESTAMP. The time interval is the time difference of a place from the GMT (Greenwich Mean Time). For example, the interval for Athens, Greece is +02:00 and the timestamp for the 11th of September of 1998, at 7.30 PM in Athens is: TIMESTAMP '1998-09-11 19:30:00+02:00'.

If the timestamp interval is not explicitly set, then the local time zone (i.e. the server time zone is used.) The user may modify the local time zone through the command SET TIME ZONE.

Mathematical functions
For making the function examples below more solid, we assume there is a table tempo, with columns angle (containing angles in radians) and number (containing numbers).

ABS(numeric_expression)

Returns the absolute value of a numeric expression.

Example:

Select ABS(number) from tempo,

returns 3.4 if the number is 3.4

and again 3.4 if the number is -3.4

ACOS(float_expression)

Returns the arccosine of float_expression as an angle, expressed in radians. If the argument is not within the domain range (-1, 1), then a DOMAIN ERROR message is returned.

 

Example:

Select ACOS (angle) from tempo,  on angle 0, returns pi/2.

 

ASIN(float_expression)

Returns the arcsine of float_expression as an angle, expressed in radians. If the argument is not within the domain range (-1, 1), then a DOMAIN ERROR message is returned.

 

Example:

Select ASIN (angle) from tempo, on angle pi, returns 0.

 

ATAN(float_expression)

Returns the arctangent of float_expression as an angle, expressed in radians.

 

Example:

Select ATAN(number) from tempo, on number 1, returns pi/4.

 

ATAN2(float_expression1, float_expression2)

Returns the arctangent of x and y coordinates, specified by float_expression1 and float_expression2, respectively, as an angle, expressed in radians. When both x and y coordinates are zero (0), a DOMAIN ERROR message is returned.

 

Example:

ATAN2 (Xcoord, Ycoord) from tempo, on Xcoord = 10 and Ycoord = 10  returns 1.570796.

 

CEILING(numeric_expression)

Returns the smallest integer greater than or equal to numeric_expression.

 

Example:

Select CEILING (number) from tempo, on number = 5.4 returns 6.

Select CEILING (number) from tempo, on number= -5.4 returns -5.

 

COS(float_expression)

Returns the cosine of float_expression, where float expression is an angle expressed in radians. If the argument is either 0 or pi, then a DOMAIN ERROR message is returned.

 

Example:

Select COS (angle) from tempo, on angle pi/2, returns 0.

 

COT(float_expression)

Returns the cotangent of float_expression, where float_expression is an angle expressed in radians.

 

Example:

Select COT (angle) from tempo, on angle pi/4,  returns 1.

 

DEGREES(numeric_expression)

Returns the number of degrees converted from numeric expression to radians.

 

Example:

Select DEGREES (1) from tempo, returns 57.2957.

 

EXP(float_expression)

Returns the exponential value of float_expression. In case of a result outside the computer's bounds, a BOUND ERROR message is returned.

 

Example:

Select EXP (number) from tempo, on number 4, returns e^4.

FLOOR(numeric_expression)

Returns the largest integer less than or equal to numeric_expression.

 

Example:

Select FLOOR (number) from tempo, on number 3.4 returns 3.

Select FLOOR (number) from tempo, on number -3.4 returns -4.

 

LOG(float_expression)

Returns the natural logarithm of float_expression. If the argument is negative, then a DOMAIN ERROR message is returned.

 

Example:

Select LOG (number) from tempo, on number e³, returns 3.

 

LOG10(float_expression)

Returns the base 10 logarithm of float_expression. If the argument is negative, then a DOMAIN ERROR message is returned.

 

Example:

Select LOG10 (number) from tempo, on number 1000,  returns 3.

 

MOD(integer_expression1, integer_expression2)

Returns the remainder (modulus) of integer_expression1 divided by integer_expression2. If integer_expression2 is 0, then a DIVISION BY ZERO ERROR message is returned.

 

Example:

Select MOD (number, 3) from tempo, on number 20 returns 2.

 

PI()

Returns the constant value of pi as a floating point value. The accuracy of the PI() function depends on the computer's accuracy.

 

Example:

Update tempo Set angle =  PI() sets angle to 3.14159...

 

POWER(numeric_expression, integer_expression)

Returns the value of numeric_expression to the power of integer expression. If numeric_expression is zero and integer_expression is less than zero, then a DOMAIN ERROR message is returned. In case of a result outside the computer's bounds, a BOUND ERROR message is returned.

 

Example:

Select POWER (base, exponent) from tempo, on base 2, exponent 8 , returns 256

 

RADIANS(numeric_expression)

Returns the number of radians converted from numeric expression to degrees.

 

Example:

Select RADIANS (100) from tempo returns 1.7453

 

RAND([integer_expression])

Returns a random floating point value, using integer_expression as the optional seed value.

 

ROUND(numeric_expression, integer_expression)

Returns numeric expression rounded to integer_expression places right of the decimal point. If integer_expression is negative, numeric expression is rounded |integer_expression| places to the left of the decimal point.

 

Example:

Select ROUND (number, 2) from tempo, on number 7.1278,2 returns 7.13

Select ROUND (number, -2) from tempo, on number 7.1278,2 returns 0

 

SIGN(numeric_expression)

Returns an indicator or the sign of numeric_expression. If numeric_expression is less than 0, -1 is returned. If numeric expression equals 0, 0 is returned. If numeric_expression is greater than 0, 1 is returned.

 

Example:

Select SIGN (number) from tempo, on number 10, returns 1

Select SIGN (number) from tempo, on number 0, returns 0

Select SIGN (number) from tempo, on number -10, returns -1

 

SIN(float_expression)

Returns the sine of float_expression, where float_expression is an angle expressed in radians.

 

Example:

Select SIN (angle) from tempo, on number pi/2, returns 1

 

SQRT(float_expression)

Returns the square root of float_expression. If the argument is negative, then a DOMAIN ERROR message is returned. In case of a result outside the computer's bounds, a BOUND ERROR message is returned.

 

Example:

Select SQRT (number) from tempo, on number 16, returns 4

 

TAN(float_expression)

Returns the tangent of float_expression, where float_expression is an angle expressed in radians.

 

Example:

Select TAN (angle) from tempo, on number pi/4,  returns 1

 

TRUNCATE(numeric_expression, integer_expression)

Returns numeric_expression truncated to integer_expression places right of the decimal point. If integer_expression is negative, numeric_expression is truncated to |integer_expression| places to the left of the decimal point.

 

Example:

Select TRUNCATE (number, 2) from tempo, on number 7.1278.2 returns 7.12

Select TRUNCATE (number, -2) from tempo, on number 7.1278.2 returns 0

_HOUR(time_expression)

Returns the hour in time_expression as an integer value in the range of 0-23.

 

Example:

Select _HOUR (time_field) from tempo, on time_field 22:17:21, returns 22

 

_MINUTE(time_expression)

Returns the minute in time_expression as an integer value in the range of 0-59.

 

Example:

Select _MINUTE (time_field) from tempo, on time_field 22:17:21, returns 17

 

_MONTH(date_expression)

Returns the month in date_expression as an integer value in the range of 1-12.

 

Example:

Select _MONTH (date_field) from tempo, on date_field 1998:10:12, returns 10

 

_SECOND(time_expression)

Returns the second in time_expression as an integer value in the range of 0-59.

 

Example:

Select _SECOND (time_field) from tempo, on time_field 22:17:21, returns 21

 

_YEAR(date_expression)

Returns the year in date_expression as an integer value. The range is data source_dependent.

 

Example:

Select _YEAR (date_field) from tempo, on date_field 1998-10-12, returns 1998

 

CURDATE()

Returns the time when the statement has begun as a date value.

 

Example:

If a statement began at 10:20 am on October 15th 1998, then

Select CURDATE () from tempo, returns 1998-10-15

 

CURTIME()

Returns the local time when the statement has begun as a time value.

 

Example:

If a statement began at 10:20 am on October 15th 1998, then

Select CURTIME () from tempo, returns 10:20:00

 

CURTIMESTAMP()

Returns the time where the statement has begun as a timestamp value.

 

Example:

If a statement began at 10:20 am on October 15th 1998, then

Select CURTIMESTAMP () from tempo, returns 1998-10-12 10:20:00

 

DAYNAME(date_expression)

Returns a character string, containing the name of the day (in the Sun through Sat form) for the day portion of date_expression.

 

Example:

Select DAYNAME (date_field) from tempo, on date_field 1998-10-12,  returns Mon

DAYOFMONTH(date_expression)

Returns the day of the month in date_expression as an integer value in the range of 1-31.

Example:

Select DAYOFMONTH (date_field) from tempo, on date_field 1998-10-12, returns 12

DAYOFWEEK(date_expression)

Returns the day of the week in date_expression as an integer value in the range of 1-7, where 1 represents Sunday.

Example:

Select DAYOFWEEK (date_field) from tempo, on date_field 1998-10-12, returns 2

DAYOFYEAR(date_expression)

Returns the day of the year in date_expression as an integer value in the range of 1-366.

Example:

Select DAYOFYEAR (date_field) from tempo, on date_field 1998-10-12, returns 41

MONTHNAME(date_expression)

Returns a character string containing the name of the month (in the Jan through Dec form) for the month portion of the date_expression.

Example:

Select MONTHNAME (date_field) from tempo, on date_field 1998-10-12, returns Oct

NOW()

Returns the current date and time as a timestamp value.

Example:

Select NOW() from tempo

on December 2, 1998 at 09:30:22 returns 1998-12-02 09:30:22

QUARTER(date_expression)

Returns the quarter in date expression as an integer value in the range of 1-4, where 1 represents January 1 through March 31.

Example:

Select QUARTER (date_field) from tempo, on date_field 1998-10-12, returns 4

TIMESTAMPADD(interval, integer_expression, timestamp_expression)

Returns the timestamp calculated by adding integer_expression intervals of type interval to timestamp_expression. Valid values of interval are the following keywords:

TIMESTAMPDIF(interval, timestamp_expression1, timestamp_expression2)

Returns the integer number of intervals of type interval by which timestamp_expression2 is greater than timestamp_expression1. Valid values of interval are the following keywords:

WEEK(date_expression)

Returns the week of the year in date_expression as an integer value in the range of 1-53.

Example:

Select WEEK (date_field) from tempo, on date_field 1998-10-12 returns 2

Note!

The user should note that string_expressions are either ascii_expressions or unicode_expressions. Whenever a function requires more than one argument, an attempt of conversion from one to another is made under certain conditions, before the operation is performed. Only a very small Unicode number (first 8-bit useful information) may be correctly converted to ASCII. Only 7-bit ASCII character (most significant bit 0) may be converted to an equivalent Unicode.

ASCII(string_expression)

Returns the ASCII code value of the leftmost character of string_expression as an integer if the string is ASCII. If the string is a binary_expression, then the ASCII code value of the first byte is returned. If the string is a unicode_expression, then the Unicode value of the leftmost character string is returned.

 

Example:

Select ASCII (name) from tempo, on name 'Alpha',  returns 65

 

CHAR_LENGTH(string_expression)

Returns the number of characters in string_expression, excluding trailing blanks and the string termination character.

Example:

Select CHAR_LENGTH (name) from tempo, on name 'Good ',  returns 4

CHARACTER_LENGTH(tring_expression)

Returns the number of characters in string_expression, excluding trailing blanks and the string termination character.

Example:

Select CHARACTER_LENGTH (name) from tempo, on name 'Good ',  returns 4

CONCAT(string_expression, string_expression)

Returns a character string that is the result of concatenating string_expression2 to string_expression1. See note above for conversion conditions.

Example:

Update tempo Set name = CONCAT ('Alpha', ' and Beta') sets name to 'Alpha and Beta'

LCASE(string_expression)

Converts all upper case characters in string expression to lower case. Applies only to ascii_expressions.

Example:

Select LCASE (name) from tempo, on name 'ZERO', returns zero

_LENGTH(string_expression)

Returns the number of characters in string_expression, excluding trailing blanks and the string termination character.

Example:

Select _LENGTH (name) from tempo, on name 'Good ',  returns 4

LOCATE(string_expression1, string_expression2, [start])

Returns the starting position of the first occurrence of string_expression1 within string_expression2. The search for the first occurrence of string_expression1 begins with the first character position in string_expression2 unless the optional argument start is specified. If start is specified, the search begins with the character position indicated by the value of start. The first character position in string_expression2 is indicated by the value 1. If string_expression1 is not found within string_expression2, the value 0 is returned. See note above for conversion conditions.

Example:

LOCATE ('ha', 'Alpha') returns 4

LOWER(string_expression)

Converts all upper case characters in string_expression to lower case. Applies only to ascii_expressions.

Example:

Select LOWER (name) from tempo, on name 'ZERO', returns 'zero'

LTRIM(string_expression)

Returns the characters of string_expression, with leading blanks removed.

Example:

Select LTRIM (name) from tempo, on name ' XX', returns 'XX'

_CHAR(integer_expression)

Returns the corresponding ASCII character to integer_expression.

Example:

_CHAR (66) returns 'B'

REPLACE(string_expression1, string_expression2, string_expression3)

Replaces all occurrences of string_expression2 in string_expression1 with string_expression3. See note above for conversion conditions.

Example:

REPLACE ('Alpha', 'Alph', 'Bet')  returns 'Beta'

RTRIM(string_expression)

Returns the characters of string_expression with trailing blanks removed.

Example:

Select RTRIM (name) from tempo, on name 'Good ', returns 'Good'

_INSERT(string_expression1, start, length, string_expression2)

Returns a character string where length characters have been deleted from string_expression1, beginning at start and where string_expression2 has been inserted into string_expression1, beginning at start. See note above for conversion conditions.

Example:

Suppose column name contains 'Good Morning', then

Update tempo set name = INSERT (name, 6, 3, 'Eve')

returns 'Good Evening'

_LEFT(string_expression, count)

Returns the leftmost count of characters of string_expression.

Example:

Select _LEFT (name, 6) from tempo, on name 'Good Morning', returns 'Good M'

_RIGHT(string_expression, count)

Returns the rightmost count of characters of string_expression.

Example:

Select _RIGHT (name, 7) from tempo, on name 'Good Morning' returns 'Morning'

_SUBSTRING(string_expression, start, length)

Returns a character string that is derived from string_expression, beginning at the character position specified by start for length characters.

Example:

Select _SUBSTRING (name, 6, 7) from tempo, on name 'Good Morning Friend', returns 'Morning'

UCASE(string_expression)

Converts all lower case characters in string_expression to upper case. Applies only to ascii_expressions.

Example:

Select UCASE (name) from tempo, on name 'Alpha', returns 'ALPHA'

UPPER(string_expression)

Converts all lower case characters in string_expression to upper case. Applies only to ascii_expressions.

Example:

Select UPPER (name) from tempo, on name 'Alpha', returns 'ALPHA'

For a better presentation of the functions below, we assume there exists a table testpic, created as follows:
create table testpic (
id integer,
picture long varbinary);

Later on, a gif/image type BLOB has been inserted into the table with id =1.

HTMLREF(long_expression, ascii_expression)

The function obtains the URI address of the long_expression that is the BLOB, and returns it in the form of an HTML reference with the ASCII expression concatenated to it.

Example:

select htmlref (pic, 'xxx') from testpic where id =1, returns:

'<a href="http://0.0.0.0:8181/blob1acbe23f2"xxx</a

IMAGE(long_expression)

Function IMAGE checks if the long_expression that is the BLOB is indeed an image through examination of the MIMETYPE of the BLOB argument. If not, it returns a DOMAIN ERROR message. Otherwise, it returns a tag of the form "<img src=\xxx", where xxx is the URI address of the image BLOB. An example of a valid MIMETYPE is e.g. image/gif.

Example:

select image(pic) from testpic where id=1, returns:

'<img src="http://0.0.0.0.8181/blob1acbe23f2"'

Returns the MIMETYPE of the BLOB as a character string.

Example:

select mimetype(pic) from testpic where id=1, returns:

'image/gif'

URI(long_expression)

Returns the URI address of the BLOB, as a character string.

Example:

select uri(pic) from testpic where id = 1, returns:

http://0.0.0.0.8181/blob1acbe23f2

DATABASE()

Returns the name of the database corresponding to the connection handle.

Example:

Select database() from tempo, returns the name of the database, e.g. testbase.

IFNUL(expression, value)

If expression is null, value is returned. If expression is not null, expression is returned. The possible data type(s) of value must be compatible with the data type of expression.

For data definition, the SQL/92 synonyms are supported for the following types:

• dec = decimal
• int = integer
• char = character
• double precision = double

• char = char(1)
• same for varchar, binary, varbinary
• numeric(5) = numeric (5,0)
• numeric = numeric (17,0)
• same for decimal

User management in Ovrimos is performed with simple commands. One extra feature, found in Ovrimos is the option of privilege inheritance. Specific users can be designated as prototype users, typically with a null password that disallows logging in. Users created like this user, inherit his privileges. This makes it easy to administer privileges for groups of people with a simple grant/revoke to/from the prototype user. This hierarchy can be extended to as many levels as we wish. In the future, multiple inheritance will allow the SQL3 "roles" to be implemented.

• In the beginning there is only one user, admin, with a known password, pegasus.

Users may change their password, using the following command:

• It is also possible to update the password with NULL, in which case the user may no longer connect to the database, until a new password is assigned to the user.
• The administrator may use a full version of the update command which is:

• Other users may be created using the following command:

• Users may be removed, using the following command:

    The Integrity Enhancement Facility (IEF) is implemented, but with the following shortcomings:

• There are no default values.
• There is a check on tables during insert and update, but it functions only for table fields. In particular, the check expression should not refer to the table as a whole when its result depends on the row inserted or updated.

System tables

Each database has its own built-in set of system tables, i.e. tables that contain data about the users, tables, columns, keys, references, views, etc.
These tables belong to each database exclusively and there is no clash of namespaces and privileges between databases.
System tables are visible to users and their columns displayed.

The system tables in version 2.5 of Ovrimos are:

sys.users
sys.tables
sys.columns
sys.table_privileges
sys.column_privileges
sys.keys
sys.key_columns
sys.foreign_keys
sys.fkey_columns
sys.blobs
sys.views
sys.view_refs
sys.data_types
sys.domains

Users who wish to explore the system tables have to visit the Roadmap, where the tool for exploration of tables, views and users is described.

Warning! Users must never directly update system tables through SQL commands. Administrators, wishing to modify user data must always do so from the specifically provided Ovrimos SQL commands. See above, Privilege Inheritance.
 
 
 

Previous Chapter: Questions & Answers

Next Chapter: Appendix - Architecture