Copyright © 2001, 2002
Harald Wabnig
email: wabnig@testready.net
homepage: www.testready.net
Speedy1-Database User Documentation
Speedy1-Database User Documentation
Speedy1-Database Shell Program “speedysh1”
Information about Batchfile-Definition
Speedy1 SQL-Interface Language Definition
General Advice
Speedy allows the use of unique indices and indices.
Unique indices only speedy up evaluation if the index only contains one
attribute. If the unique index contains more than one attribute you have to use
another (non-unique) index on the attributes. Be aware that each (non-unique)
index is internally mapped to single attribute indexes (but you can define more
than one index with for an attribute; the index is only hold once). You can
drop and create indices on demand. The indices are stored for static/secure
tables. Loading of large databases is optimised.
Speedy evaluates the queries from left to right. So to
speed up evaluation you should use an index and formulate the query from more
specific to general:
e.g. Assume that there are rows with ascending integer
values ival from 1 to 100000. You should write a select like SELECT ival WHERE
ival < 100 AND ival > 20 rather than ival > 20 AND ival < 100.
Installation
Download the Speedy1-package appropriate for your
System. Packages for Linux (speedy1-x.x.x.zip) and Windows
(speedy1_win-x.x.x.zip) are available. All packages are compressed in the
zip-format. So you need a zip-utility to uncompress (e.g. Winzip under Windows
or “unzip speedy1-x.x.x.zip” under Linux).
Uncompress the package to a new directory. The
file-structure contains sub-directories which holds the binaries and
library-files.
Installation under Windows
If you want access to the Speedy1-package-binaries
from the MSDOS-shell you must add the path to the binary-directory to the PATH-environment-variable
in the “autoexec.bat”-file in the root directory of drive C.
To set up
the paths you should edit the file “autoexec.bat” for example in the following
way:
[…]
SET
PATH=<existing PATH>;<Speedy1-path>\bin;
SET
DBDAT_PATH=<Speedy1-path>\include\dat
[…]
See also the Speedy1-Database-Programmer-Documentation
for installation for developing applications.
Installation under Linux
You must add the path to the Speedy1-package-binaries
to the PATH-environment-variable. You can do this automatically by putting the
command in the “.profile”-file in your home-directory.
To set up
the paths you should edit the file “.profile” in the following way:
[…]
export
PATH=.:<Speedy1-path>/bin:$PATH
export
DBDAT_PATH=<Speedy1-path>/include/dat
[…]
See also the Speedy1-Database-Programmer-Documentation
for installation for developing applications.
Speedy1-Database
Speedy1 is an application internal main memory
database. Nevertheless the database layout and selected user configured tables
can be stored to disk. If and where the database shall be stored is defined
when the Speedy1-database is initialised (using the function Speedy1_Init).
If you want to store to disk you must define the
directory-name where all the Speedy1-data shall be stored. The directory-path
of this data-directory can be given directly to the Speedy1_Init-function. If only the directory-name is provided at
initialisation Speedy1 checks if the environment-variable SPEEDY1_PATH is
specified and uses the path given there to locate the data-directory. If this
environment variable is not specified the data is stored in the home-directory
of the user (you must set the environment-variable HOME under Windows). If this
information is also missing the data-directory is the current directory (i.e.
the directory from which the application using Speedy1 is invoked).
Speedy1-Database Limitations
General
The maximum length of Identifiers is 32
characters
The maximum length of a database-query
should not be more than 10000 characters
The maximum number of concurrently open
connections is 15
The maximum number of concurrently open
cursors per connection is 20
The maximum number of databases is 15
The maximum number of tables which can be
changed within a transaction is 256
The maximum number of attributes for a
single table is 256
Limitations on Results to
Select-Commands
The maximum number of results from a
select is 30000 rows (you can use the special SQL-command “MAX <number>”
to define another limit)
The maximum number of intermediary results
from sub-selects is 1000000
Speedy1-database uses no more memory than
the sum of the physical and the virtual memory divided by 10 with 8 MB as a
minimum; if more memory is required to evaluate a select-command the result-set
will be not fully evaluated
The maximum number of functions allowed in a
select-command is 10
The maximum number of sub-selects in a
select-command is 10
The maximum number of attribute-references in
sub-selects to attributes to upper-level (sub-)selects is 20
The maximum number of elements in the
WHERE-evaluation-stack is 100
Speedy1-Database Shell Program “speedysh1”
On the one hand the program “speedysh1” is an example
for the usage of the Speedy1-SQL-database. You can create databases (e.g. “speedysh1
CREATE testdb”), destroy databases (e.g. “speedysh1 DESTROY testdb”),
interactively work with database or execute commands from a file (e.g.
“speedysh1 testdb –Isql_file”).
On the other hand you can use the Speedy1-Database to
connect to a database which is managed by a Speedy1-application. To do this you
must specify the data-directory of the Speedy1-database on the command-line
(with option –d).
Note: The application may not change the
database-information while speedysh1 is running. Doing so will destroy prevent
one of the two applications to store their changes.
Command-line Parameters
General format: speedysh
dbname [options]
Possible options:
-?:
Prints information on command-line options.
dbname:
The name of the database to interact. The database must exists.
CREATE:
Creates a new database with name <dbname>.
DESTROY:
Destroy the existing database with name <dbname>.
-d<data-directory>:
The (path and) name of the Speedy1-data-directory.
-i<filename>:
The file <filename> is processed instead of keyboard input.
-s|-silent:
The typed SQL-commands are not echoed to the output.
Information about Batchfile-Definition
You may not give more than one command in
a line.
You must finish each command with a ”;”.
Lines beginning with "--" are
comments.
Lines beginning with "#" marks
this line to be the end of the file.
Speedy1
SQL-Interface Language Definition
Lexical parser
upper or
lower case is accepted (internally only upper-case is used)
Keywords
ABORT, ALL,
AND, ASC,
BEGIN,
BETWEEN, BINARY,
CHARACTER,
CREATE (CREATE TABLE, CREATE INDEX, CREATE UNIQUE INDEX),
COUNT,
COMMIT, CURSOR, CLOSE,
DATE, DROP
(DROP DATABASE, DROP TABLE, DROP INDEX),
DELETE
(DELETE FROM), DECLARE, DESC, DATABASE, DATABASES, DISTINCT,
END,
EXISTS, EXPLAIN,
FROM,
FLOAT, FOR, FETCH,
INSERT
(INSERT INTO), INTEGER, IN, INDEX, INDICES,
LIKE,
MIN, MAX,
NOT, NULL
ORDER
(ORDER BY), OR, ON,
SMALLINT,
SELECT, SET, STATIC, SAVE, SECURE, STOP
TIME,
TABLE, TIMESTAMP, TABLES,
UPDATE,
WHERE,
VALUES
Identifiers
(a..z,
A..Z)[a..z, A..Z, _, 0..9]*
must be
different to keywords
maximum
length is 32 characters
Strings
"'" [ <all characters except "'"; use
"\" to quote characters> ] "'"
Integer
values
["-"](0..9)[0..9]*["E"["+"|"-"](0..9)[0..9]*]
Remark:
If the integer-value exceeds the integer range, it is treated as double.
Double
values
["-"](0..9)[0..9]*"."(0..9)[0..9]*["E"["+"|"-"](0..9)[0..9]*]
Remark:
If the integer-value exceeds the integer range, it is treated as double.
Datatypes
CHARACTER(<size>)
Constant
definition: see "Strings"
Range: size
must be smaller than 10000 characters
NULL-value:
''
Operators:
"<", ">", "=", "<=",
">=", "<>", LIKE
Examples:
'hello', 'from \'A\' to \'B\''
BINARY(<size>)
Constant
definition: characters from “ “ to “~” can be defined literally;
single binary values can be quoted by “$”;
sequences of binary values are quoted by
“§” at the beginning and “§” at the end;
“$” is defined as “$$”; “§” is defined as
“§§”;
binary characters are encoded in
hexadecimal form with two digits
Range: size
must be smaller than 10000 characters
NULL-value:
''
Operators:
"<", ">", "=", "<=",
">=", "<>", LIKE
Examples:
'hello', 'from \'A\' to \'B\''
SMALLINT
Constant definition:
see "Integer values"
Range: as
short in C-language
NULL-value:
-32768
Operators:
"<", ">", "=", "<=",
">=", "<>"
Examples:
-60, 0, +50, 7000, 1e3, 10E-0
INTEGER
Constant
definition: see "Integer values"
Range: as
int/long in C-language
NULL-value:
-2147483647L
Operators:
"<", ">", "=", "<=",
">=", "<>"
Examples:
-60, 30, -10e7, 576, -5000, +20
FLOAT
Constant
definition: see "Double values"
Range: as
double in C-language
NULL-value:
-1.e+20
Operators:
"<", ">", "=", "<=",
">=", "<>"
Examples:
-1e20, 2.25, -5.60, 1000.15, 0.1, 1.45e-10
DATE
Constant
definition: 'yyyy-mm-dd'
Range: year
must be within 1800 and 2800
NULL-value:
'1800-01-01'
Operators:
"<", ">", "=", "<=",
">=", "<>"
Examples:
'2001-04-19', '1998-05-15'
TIME
Constant
definition: 'hh:mm:ss'
Range: no
limitations
NULL-value:
'00:00:00'
Operators:
"<", ">", "=", "<=",
">=", "<>"
Examples:
'15:20:15'
TIMESTAMP
Constant definition:
'yyyy-mm-dd-hh.mm.ss.uuuuuu'
Range: year
must be within 1800 and 2800
NULL-value:
'1800-01-01-00.00.00.000000'
Operators:
"<", ">", "=", "<=",
">=", "<>"
Examples:
'2001-04-19-15.20.15.123456', '1998-05-15-01.23.10.532010'
Syntax of SQL-commands
Where-expressions:
where-expression = expression [ AND expression ]* |
expression = "(" expression ")" |
NOT expression |
expression [ OR expression ]* |
operand [ operator operand ] |
identifier operator "(" subselect ")" |
EXISTS "(" subselect ")" |
identifier IN "(" subselect ")" |
identifier IN "(" constant [
"," constant ]* ")" |
identifier LIKE constant |
identifier BETWEEN constant AND constant
operator
= "<" | ">" | "=" | "<=" |
">=" | "<>"
operand =
[ identifier "." ] identifier |
constant
subselect
= SELECT [ DISTINCT ] select-column
FROM
identifier [ identifier ] [ "," identifier [ identifier ] ]*
[
WHERE where-expression ]
Select-column:
[
identifier "." ] identifier |
[ identifier
"." ] "*"
"*" |
COUNT
"(" "*" ")" |
MIN
"(" [ identifier "." ] identifier ")" |
MAX
"(" [ identifier "." ]
identifier ")" |
CREATE
TABLE identifier (
identifier
datatype [ NOT NULL ]
[
"," identifier datatype [ NOT NULL ] ]*
)
Meaning: creates
a new table with the given attributes
Example: CREATE
TABLE table1 (code CHARACTER(4) NOT NULL);
CREATE
TABLE table2 (code CHARACTER(4), small SMALLINT NOT NULL);
DROP TABLE
identifier
Meaning:
destroys the existing table named <identifier>; all indices of
the table are automatically destroyed;
Example: DROP
TABLE table1;
CREATE [
UNIQUE ] INDEX identifier ON identifier (
identifier
[ "," identifier ]*
)
Meaning:
creates a new index on a table
Example: CREATE
INDEX table1idx1 ON table1 (code);
CREATE
UNIQUE INDEX table2idx1 ON table2 (code, small);
DROP INDEX
identifier
Meaning:
destroys the existing table-index
Example: DROP
INDEX table1idx1;
Remark: index
names must be unique for the whole database
Select-command:
SELECT
[ DISTINCT ] select-column [ "," select-column ]*
FROM
identifier [ identifier ] [ "," identifier [ identifier ] ]*
[
WHERE where-expression ]
[ ORDER
BY [ identifier "." ] identifier [ ASC | DESC ]
[ "," [ identifier "." ] identifier [ ASC | DESC
] ]*
Meaning:
selects rows from a table; the where-expression can be used to
defines which rows to select; the order-expression can be used
to
sort the select-results;
Example: SELECT
* FROM table1;
SELECT
DISTINCT code FROM table1 ORDER BY code;
(more examples are given later on)
BEGIN
Meaning:
starts a transaction; you can only issue database changes and
cursor-selects within transactions;
Example: BEGIN;
END
Meaning:
successfully finishes a transaction; all database changes are
performed; similar functionality to the COMMIT command;
Example: END;
COMMIT
Meaning:
successfully finishes a transaction; all database changes are
performed;
Example: COMMIT;
ROLLBACK
Meaning: terminates
a transaction; all database changes within the transaction
are discarded;
Example: ROLLBACK;
DECLARE
identifier CURSOR FOR select-command
Meaning:
defines a cursor for accessing subsets of the results from a
select command; this command can only be issued within a transaction;
Example: DECLARE
cur1 CURSOR FOR SELECT * FROM table1;
FETCH
[ ALL ] IN identifier
Meaning:
reads from an open cursor one row; to read all remaining rows
use the keyword ALL;
Example: FETCH
IN cur1;
FETCH ALL IN cur1;
CLOSE
identifier
Meaning:
closes an opened cursor; a cursor is automatically closed when
the corresponding transaction is finished; a cursor can be closed
before all result rows are evaluated;
Example: CLOSE
cur1;
INSERT
INTO identifier [ "(" identifier [ ","
identifier ]* ")" ]
VALUES
"(" constant [ "," constant ]* ")"
Meaning:
inserts a new row into a table; all attributes which are
Defined as NOT NULL must be defined; insert is only possible
if
all existing unique indices allows the operation;
this
command can only be issued within a transaction;
Example: INSERT
INTO table1 VALUES ('abcd');
INSERT
INTO table1 (code) VALUES ('10');
INSERT
INTO table2 VALUES ('', 10);
INSERT
INTO table2 (small) VALUES (10);
UPDATE
identifier SET identifier "=" constant [ ","
identifier "=" constant ]*
WHERE
where-expression
Meaning:
updates rows from a table; the where-expression can be used to
defines which rows to update;
this
command can only be issued within a transaction;
Example: UPDATE
table1 SET code = '20';
UPDATE
table2 SET small = '20' WHERE small = 10;
UPDATE
table2 SET small = '20', code = '20' WHERE small = 10;
DELETE
FROM identifier
WHERE
where-expression
Meaning:
deletes rows from a table; the where-expression can be used to
defines which rows to delete;
this
command can only be issued within a transaction;
Example: DELETE
FROM table1;
DELETE
FROM table2 WHERE small
Special database commands
CREATE
DATABASE identifier;
Meaning:
creates a new database named <identifier>
Example: CREATE
DATABASE db1;
DROP
DATABASE;
Meaning:
destroys an existing database (the one which is actually
connected)
Example:
DROP DATABASE;
SET DATABASE identifier;
Meaning:
connects the application to database <identifier>
Example: SET
DATABASE db1;
SET TABLE
<tablename> STATIC;
This
command defines that a table is static. This means that the
content of
the table shall be conserved even if the database-server
is stopped.
All static tables will keep their contents, all other
tables will
be empty upon restart of the database-server.
Note: The
table-structure (attributes and indices) is always saved.
SET TABLE
<tablename> SECURE;
This
command defines that a table is secure. This means that the
content of
the table shall be conserved even if the computer has
a severe
error (power off, hard-reboot). All secure tables will
keep their
contents, all other tables (even if declared as static)
are empty
or may loose data.
Note: The
table-structure (attributes and indices) is always saved.
SAVE
DATABASE;
This
command saves the actual contents of the tables which are defined
static.
MAX
<max-results>;
This
command defines the maximum number of result rows returned from
a
select-query. Defining zero deactivates the limitation of row
numbers.
SHOW
DATABASES;
This command
lists all databases.
SHOW
TABLES;
This
command lists all tables of the active database. In addition to
each table
the information is given whether it is a static table.
SHOW
INDICES;
This
command lists all indices of the active database. In addition to
each index
the information is given to which table the index belongs.
SHOW TABLE
<tablename>;
This
command lists all attributes of the given table in the active
database. The
datatype, the size for character-attributes and the
information
whether the attribute can be null is given.
SHOW
INDICES ON TABLE <tablename>;
This
command lists all indices of the given table in the active
database.
SHOW INDEX
<indexname>;
This
command lists all attributes of the given index in the active
database.
The datatype, the size for character-attributes and the
information
whether the attribute can be null is given.