URL: http://www.firstbasesoftware.com/man/man1/dbsql.htm
Last modified: 12 September 1995
Copyright © by FirstBase Software.
[ Index of Contents] [ FirstBase RDBMS Overview]

NAME

SYNOPSIS

dbsql

[-f script_file] [-P] [-t temp_dir] [-p page_length] [-w page_width] [-h] [-i indent]
[-mN margin_value] [-e] [-c char_sep] [-n] [-q] [-v] [-H] [-HBN] [-HCN]

DESCRIPTION

Dbsql is a non-procedural language and command interpreter that allows complex database relations involving any number of databases to be queried, displayed, or even stored as new FirstBase data objects. Every dbsql statement is executed -- results are either projected (displayed) or stored.

Although the standard for SQL uses only uppercase, dbsql will handle both uppercase and lowercase. However, in this manual page all key words will still appear in uppercase.

In interactive mode, dbsql will provide a dbsql> prompt. Each statement, entered on as many lines as needed and terminated with a semicolon, is then executed as a dbsql command. Once the result is projected or stored, the prompt is redisplayed.

To exit interactive dbsql use an end-of-file keystroke (Control-D) or either the quit or exit directives. A full list of directives appears below.

Stored dbsql commands or scripts can be processed using the -f switch. During this batchmode processing, dbsql will execute each statement, projecting results to standard output. If this output is redirected, dbsql could run as a background process.

Some parts of dbsql default behavior are controlled by the environment. The pagelength and width of the display device are determined at run time using termcap(3) routines. These variables can also be controlled by the -p and -w switches.

The environment variable PAGER is also used for the -P option. This option, effective only in interactive mode, will use a standard UNIX paging tool (like more(1)) to display the results of a request. This feature is quite useful if the results to a query are many pages long.

Formatted output using dbsql is done using a mixture of switch settings and augmentations to the standard SQL SELECT statement. First, the -h must be specified to enable report headers to be generated. Then, indentation is controlled by -i.

Top and bottom margins are manipulated using the -mN format where N is 1 through 4 depending on which section the margin_value is going to represent: top of page to header, header to top of text body, bottom of text body to footer, footer to bottom of page. The default for each of these four margin values is 2 (for two lines of space in each place).

For more information on formatted output, see the section entitled REPORT GENERATION below.

When dbsql is executing statements, temporary files, databases and indexes are sometimes created. The default area for this storage is in /usr/tmp, but can be controlled with the -t switch.

The default output of dbsql is formatted in columns, if the result will fit horizontally on the display device. Otherwise, the output is displayed vertically.

However, by using the -e switch, dbsql can be forced to generate standard FirstBase dbemit(1) style output. This output is one record per line, with tokens separating the fields. The default token is a comma, though the -c option controls this character separator.

The -H option turns on HTML output mode. In this mode, all output will be as HTML documents with a table representing the results. The -HB option places a border around the table, with the width specified by any connected digits, for example -HB2. In a similar manner, -HC controls table cell padding.

To communicate with dbsql using a CGI (Common Gateway Interface) request, use dbmacro(1). One method is to generate the needed dbsql query into one temporary file, and the command file that executes the query into a different temporary file, use the dbmacro function popen on the command file, read all output from the process with fgets and print this output to standard out using the dbmacro function puts or printf.

Other option switches affecting the dbemit(1) style output are -q for forcing quotes around all alpha type fields and -v for formatting date and dollar type fields. The -n switch is used to force the long fields to print using the string "\n" in place of newlines. This format is understood by dbload(1).

DIRECTIVES

cd

Change to directory indicated.

pwd

Print working directory.

help

Provide help mechanism. Help on commands and databases is displayed using this command. For example, help select and help dbase are two valid uses of the help command.

exit

Exit from the dbsql session. Same as quit.

!line

Submits line as a command to a sub shell. For example, !mail would run the UNIX mail(1) command, returning to dbsql when completed.

#line

Lines starting with the # character are ignored as comments.

version

Print the current version number of dbsql.

SQL COMMANDS

SELECT

The SELECT command, the basic <query specification>, is the mechanism underlying most all dbsql requests. The syntax of the the SELECT command is:

<query specification> ::=
   SELECT [ ALL | DISTINCT ] <select list>
   <from clause>
   [ <where clause> ]
   [ <order by clause> ]
   [ <group by clause> ]
   [ <having clause> ]
   [ <header clause> ]
   [ <footer clause> ]

The last six clauses are all optional -- only the <from clause> is required to project (display) the <select list>. Each of the components of this <query specification> is listed in detail in the grammar. There are also many examples below.

The <where clause> is used to limit the set of records or relations displayed. The limitations described can be very complex, with AND/OR connections between sub parts. Standard math notation, set and string functions, and parenthesis for grouping are all supported.

The <order by clause> is used to display the results in a specific order. Either ascending or descending order can be specified. Note that each element of the <order by clause> must also appear in the actual >select list>, or be a numeral representing a column from this list.

The <group by clause> can be used to partition the set of found records or relations into groups. Each part of the <select list> for these requests must be single valued per group, or be a function on a field or column. The function, for example SUM, will be done for that one group only. Note that in dbsql, group by presumes order by.

The <having clause> is an optional clause used to limit the groups chosen in the <group by clause>. Only those groups passing the restrictions in this clause will be included in the final display.

The <header clause> and <footer clause> can be used to specify report headers and footers. See the section on REPORT GENERATION below.

CREATE VIEW

The CREATE VIEW command is used to generate a new FirstBase database from one or more existing databases. This command uses a <table name> and an optional <column list>, followed by a <query specification> (i.e. a SELECT command).

<view definition> ::=
   CREATE VIEW <table name>
      [ <column list> ]
      AS <query specification>

The result of the CREATE VIEW is an actual FirstBase database complete with a database dictionary. The names of the fields can be controlled (and must be! if the <select list> contains expressions as opposed to simple fields or columns) using the optional <column list> .

DROP VIEW

The DROP VIEW command will delete the listed FirstBase database. If in interactive mode, dbsql pauses for confirmation. Unlike rmdb(8), the database dictionary is also deleted using this command, i.e. all remnants of <table name> are deleted.

<drop view statement> ::=
   DROP VEIW <table name>

CREATE INDEX

The CREATE INDEX command is used to generate FirstBase indexes using dbsql command structures. The result is a true FirstBase index that can be used with any of the other FirstBase tools. The syntax of the command is:

<index definition> ::=
   CREATE INDEX <index name>
   ON     <table name>
   ( <index column> [ {,<index column>}... ] )
   [ <where clause> ]

<index column> ::=
   <column name> [ ASC | DESC ]

The <where clause> is used to limit the selected records to any subset describable using standard dbsql expressions, as done in the SELECT statement.

The result of a CREATE INDEX is the index and index header portion of a FirstBase index. Since dbigen(1) is not used during this index creation, an index dictionary, as created by dbdind(1) is not used nor generated.

DROP INDEX

The DROP INDEX command will delete the listed FirstBase index. If in interactive mode, dbsql pauses for confirmation. All remnants of <index name> are deleted.

<drop index definition> ::=
   DROP INDEX <index name>

FUNCTIONS

ABS(x)

Returns the absolute value of x.

FORMFIELD(string, type, width)

Formats string according to standard the type, in a field width characters wide. For example, FORMFIELD(123456, "$", 10) would return the string "1,234.56". Valid types are the standard FirstBase data types.

The more useful types of data to run through FORMFIELD are $ for DOLLARS and d for DATE. Remember standard FirstBase format for DOLLARS is without the decimal, so expressions without a DOLLARS type involved will need to be multipled by 100 before running through FORMFIELD, if type $ is requested. Standard FirstBase format for DATE is MMDDYY.

LENGTH(s)

Returns the string length of s.

LOWER(s)

Returns the lowercase version of s.

POWER(x, y)

Returns x raised to the yth power.

ROUND(x, y)

Returns x rounded to y places past the decimal.

SUBSTR(s, pos, length)

Returns the sub string within s starting at pos for length characters. A negative pos marks a position at the end of the string. As examples, SUBSTR(s, 1, 5) and SUBSTR(s, -5, 5) each return strings five characters long (assuming s is at least five characters). One returns the first 5 characters of the string, the other returns the last 5 characters of the string.

SUBLINE(s, n, length)

Returns a subline from a large field or string containing newlines. This function is used to tear apart a string line by line. The nth subline component from s is returned in a field length characters long. For example, SUBLINE(longfield, 4, 70) returns the fourth line from field longfield in a 70 character wide column.

SYSDATE

A simple function that returns the current system date in standard FirstBase format, MMDDYY. SYSDATE can be used in date comparisons with other fields and dates, or it can be displayed using FORMFIELD. For example, FORMFIELD(SYSDATE, "d", 10) would return something like "01/16/91".

TRUNC(x, y)

Returns x truncated at y places past the decimal. For example, to get the integer portion of a value, use TRUNC(value, 0).

UPPER(x)

Returns the uppercase version of s.

SECURE FUNCTIONS

GROUP

return the group number for a record.

MODE

return the (numeric) mode for a record.

OWNER

return the symbolic owner name for a record.

REPORT GENERATION

Secondly, there are a number of command line switches, already mentioned above, that concern formatted output. The switches -i, -p and -w control the indentation, page length and page width. The series of -mN switches controls the four top/bottom margins.

Headers and footers are specified using the HEADER and FOOTER, as defined by the <header clause> and the <footer clause>. The literals themselves need to be of the form

   "'Left Part'Middle Part'Right Part'"

Using this three part header format to denote HEADER and FOOTER requests allows control over most all desired output styles. The left parts are left justified, the middle parts are centered, and the right parts are right justified.

Furthermore, separate EVEN and ODD headers and footers can be specified. This feature enables reports to be formatted for double sided results.

There are also a set of key words or macros that can be used in the building of the HEADER and FOOTER values. The following is a list of these macros.

$CONAME

Returns the company name, as in coname(5).

$PAGE

Returns the current page number.

$USER

Returns the effective user name.

$DATE

Returns the system date in MM/DD/YY format.

$TIME

Returns the system time in HH:MM format.

$MILTIME

Returns the system time in HH:MM:SS format.

$DAY

Returns the day of the week, i.e. "Monday".

$MONTH

Returns the month, i.e. "January".

$MM

Returns the month, i.e. "January".

$DD

Returns the numerical day within the month.

$YY

Returns the year in two digit format.

$YYYY

Returns the year in four digit format.

UNIX FEATURES

In dbsql, full UNIX path names are allowed in the <from clause>. But once used there, only the basename of the database (variable) is used in all other clauses. See the EXAMPLES below.

A side effect allowing UNIX pathnames is that the forward slash character, the `/' character, has two meanings: one inside of PATH names, the other when used as a divide symbol. Due to this dual use of the slash, white space of some kind must appear on each side of this character when used as a divide symbol.

Another feature of dbsql, partially supported by standard SQL, is the pattern matching mechanism of the LIKE predicate. This predicate, used in the <where clause>, implements pattern matching using the standard UNIX regular expression mechanism, with a slight modification to the allowed meta-characters.

Standard SQL uses only two meta-characters for its <pattern>, the % (percent) and _ (underscore). The percent matches one or more characters, the underscore matches exactly one. These meta-characters work in dbsql, along with all other standard UNIX meta characters for regular expression pattern matching (*, ., [], etc).

EXAMPLES

database1:
-- -------- ---------- ----- 
ID Date     Item       Price 
-- -------- ---------- ----- 
 1 01/02/90 book       9.00  
 2 12/21/89 tape       8.00  
 3 12/01/89 cd         9.50  
 4 02/03/90 tape       7.50  
 5 07/08/90 cd         8.99  
 6 09/08/90 tape       7.50  

database2:
-- ------------------------------ -------- 
ID Title                          Quantity 
-- ------------------------------ -------- 
 1 The Joy Of Spiders                    4 
 2 Best Of The Janes                    18 
 3 Jump - Off The Edge                  11 
 4 The Sticks - One On One              11 
 5 Molly McFaren - Live                  7 
 6 Poking Holes In A Vacuum             12 

SELECT *
FROM   database1;

SELECT ID, Item
FROM   database1
WHERE  Item = "cd";

SELECT *
FROM    database1
WHERE   Item LIKE "b%k";

SELECT *
FROM   database1
WHERE  Item IN ("book", "tape");

SELECT *
FROM   database1
WHERE  Item BETWEEN "book" AND "tape";

SELECT Date
FROM   database1
WHERE  Date > "051590" AND
       Date   < "123190";

SELECT database1.*, AVG(Price)
FROM   database1
WHERE  Price > AVG(Price);

SELECT *
FROM   database2
WHERE  NOT Quantity > AVG(Quantity);

SELECT *
FROM   database1
ORDER BY Price;

SELECT *
FROM   database2
GROUP BY Quantity HAVING Quantity = "11";

SELECT database1.*, COUNT(*)
FROM   database1
GROUP BY Item HAVING COUNT(*) > 1 ;

SELECT Quantity, SUM(Quantity)
FROM   database2
GROUP BY Quantity;

SELECT ID, Quantity, ID + Quantity * 5 LABEL AS "Quant * 5 + ID"
FROM   database2;

SELECT Item, COUNT(*)
FROM   database1
GROUP BY Item;

SELECT database1.Item, database2.Quantity
FROM   database1, database2
WHERE  database1.ID = database2.ID AND
       database1.Item = "cd" OR
       database2.Quantity BETWEEN 9 and 11;

SELECT DISTINCT database1.Item, database2.Quantity
FROM   database1, database2
WHERE  database1.ID = database2.ID AND
       database1.Item = "cd" OR
       database2.Quantity BETWEEN 9 and 11
ORDER BY database2.Quantity;

SELECT database2.Title label as "Tapes"
FROM   /usr/database/database1, ~/database2
WHERE  database1.Item = "tape" AND
       database1.ID = database2.ID;

SELECT ID, Item
FROM database1
WHERE ID IN
   (SELECT ID
    FROM database2
    WHERE Quantity > 10);

SELECT Date, SYSDATE
FROM database1;

SELECT Date, FORMFIELD(SYSDATE, "d", 6)
FROM database1;

SELECT Date
FROM database1
WHERE Date < SYSDATE;

SELECT ID, Item, Price
FROM database1
HEADER "''My Very Own Report''"
FOOTER "'$USER'$CONAME'-$PAGE-'";

SELECT ID, Title, Quantity LABEL Amount,
FORMFIELD(TRUNC((ID + Quantity) * 100.0, 0), "$", 10)
LABEL "Formatted Result"
FROM database2
HEADER ODD "'odd header'middle part'-%-'"
EVEN "'-%-'even header'right part"
FOOTER ODD "'odd footer'middle part'-%-'"
EVEN "'-%-'even footer 'right part'";

CREATE VIEW database3 (OrderDate, PtNum, Tapes) 
AS SELECT   Date, ID, Item
FROM        database1
WHERE       Item = "tape";

CREATE INDEX index3
ON     database3 (OrderDate)
WHERE  PtNum > 3;

KEY WORDS

ABS ALL AND ANY AS ASC AVG
BETWEEN BY
COUNT CREATE
DESC DISTINCT DROP
EVEN EXISTS
FOOTER FORMFIELD FROM
GEQ GROUP
HAVING HEADER
IN INDEX INTO IS
LABEL LENGTH LEQ LIKE LOWER
MAX MIN
NEQ NOT NULL
ODD OF ON OR ORDER
POWER
ROUND
SELECT SOME SUBLINE SUBSTR SUM SYSDATE
TO TRUNC
UPPER
VIEW
WHERE

OPTIMIZATION

Finally, two or more variable queries (queries involving two or more databases) that use an equivalence join, or equijoin, can be further optimized using standard FirstBase autoindexes. To utilize this feature, make sure at least one of the fields used in the join is a true FirstBase autoindex as defined in the database dictionary. This feature is well worth using, even if autoindexes need to be set up from scratch. See dbdind(1) and dbigen(1) for more details on autoindexes.

GRAMMAR

<all set function> ::=
   { AVG | MAX | MIN | SUM } ( [ ALL ] <value expression> )

<boolean factor> ::= [ NOT ] <boolean primary>

<boolean primary> ::=
   <predicate> | ( <search condition> )

<boolean term> ::=
   <boolean factor> |
   <boolean term> AND <boolean factor>

<character> ::= <digit> | <letter> | <special character>

<character representation> ::= <nonquote character> | ''

<character string literal> ::= '<character representation>...'

<column list> ::= ( <column name> [ {,<column name>}... ] )

<column name> ::= <identifier>

<column specification> ::=
   <column name> |
   <table name>.<column name> |
   <table name>.*

<comparison operator> ::= = | <> | < | > | <= | >=

<distinct set function> ::=
   { AVG | MAX | MIN | SUM | COUNT }
      ( DISTINCT <column specification> )

<exact numeric literal> ::=
   [ + | - ] <unsigned integer> [ .<unsigned integer> ] |
   [ + | - ] <unsigned integer>. |
   [ + | - ] .<unsigned integer>

<factor> ::=
   [ + | - ] <primary>

<footer clause> ::=
   FOOTER [ ODD | EVEN ] <literal> [ ODD | EVEN <literal> ]

<from clause> ::=
   FROM <table reference> [ {,<table reference>}...]

<group by clause> ::=
   GROUP BY <column specification> [ {,<column specification>}... ]

<having clause> ::=
   HAVING <search condition>

<header clause> ::=
   HEADER [ ODD | EVEN ] <literal> [ ODD | EVEN <literal> ]

<identifier> ::= <letter>
   [ { [ _ ] { <letter> | <digit> } }... ]

<label>  ::=
   LABEL [ AS ] <literal>

<letter> ::=
   <lowercase letter> | <uppercase letter>

<literal> ::=
   <character string literal> |
   <numeric literal>

<numeric literal> ::=
   <exact numeric literal>

<order by clause> ::=
   ORDER BY <sort specification> [ {,<sort specification>}... ]

<pattern> ::= <value specification>

<predicate> ::=
   <value expression> <comparison operator> <value expression> |
   <value expression> <comparison operator> <subquery> |
   <value expression> [ NOT ] BETWEEN <value expression>
      AND <value expression> |
   <value expression> [ NOT ] IN
      ( <value expression> {,<value expression>}... ) ]
   <value expression> [ NOT ] IN <subquery> |
   <column specification> [ NOT ] LIKE <pattern> |
   <column specification> IS [ NOT ] NULL |
   <value expression> <comparison operator> ALL <subquery> |
   <value expression> <comparison operator> ANY <subquery> |
   <value expression> <comparison operator> SOME <subquery> |
   EXISTS <subquery>

<primary> ::=
   <value specification> |
   <column specification> |
   <set function specification> |
   <simple function specification> |
   ( <value expression> )

<query expression> ::=
   <query term> |
   <query expression> UNION [ ALL ] <query term>

<query specification> ::=
   SELECT [ ALL | DISTINCT ] <select list>
   <table expression>
   <header clause>
   <footer clause>

<query term> ::=
   <query specification> | ( <query expression> )

<search condition> ::=
   <boolean term> |
   <search condition> OR <boolean term>

<select list> ::=
   <value expression> [ <label> ]
      [ {,<value expression> [ <label> ] }... ] | *

<set function specification> ::=
   COUNT(*) |
   <distinct set function> |
   <all set function>

<sequence number> ::= <unsigned integer>

<simple function specification> ::=
   ABS(<value expression>) |
   FORMFIELD(<value expression>, <value expression>, <value expression>) |
   LENGTH(<value expression>) |
   LOWER(<value expression>) |
   POWER(<value expression>, <value expression>) |
   ROUND(<value expression>, <value expression>) |
   SUBSTR(<value expression>, <value expression>, <value expression>) |
   SUBLINE(<value expression>, <value expression>, <value expression>) |
   SYSDATE |
   TRUNC(<value expression>, <value expression>) |
   UPPER(<value expression>)

<sort specification> ::=
   { <sequence number> | <column specification> }
   [ ASC | DESC ]

<subquery> ::=
   ( SELECT [ ALL | DISTINCT ] { <value expression> | * }
   <table expression> )

<table expression> ::=
   <from clause>
   [ <where clause> ]
   [ <order by clause> ]
   [ <group by clause> ]
   [ <having clause> ]

<table identifier> ::= <identifier>

<table name> ::=
   [ <authorization identifier>. ] <table.identifier>

<table reference> ::= <table name>

<term> ::=
   <factor> |
   <term> * <factor> |
   <term> / <factor>

<unsigned integer> ::= <digit>...

<value expression> ::= 
   <term> |
   <value expression> + <term> |
   <value expression> - <term> |
   <value expression> | <term>

<value specification> ::=
   <parameter specification> |
   <variable specification> |
   <literal>

<variable specification> ::= <variable name>

<where clause> ::=
   WHERE <search condition>

This section describes the other four commands within dbsql. All of these commands create or destroy FirstBase data objects.

<view definition> ::=
   CREATE VIEW <table name>
      [ <column list> ]
      AS <query specification>

<index definition> ::=
   CREATE INDEX <index name>
      ON <table name>
      ( <index column> [ {,<index column>}... ] )
      [ <where clause> ]

<index column> ::=
   <column name> [ ASC | DESC ]

<drop index definition> ::=
   DROP INDEX <index name>

<drop view statement> ::=
   DROP VEIW <table name>

SEE ALSO

FirstBase User's Guide and Reference Manual