Speedy User Documentation

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 Speedy-package appropriate for your System. Packages for Linux (speedy-x.x.x.zip) and Windows (speedy_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 speedy-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 Speedy-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>;<Speedy-path>\bin;

SET DBDAT_PATH=<Speedy-path>\include\dat

[…]

See also the Speedy-Database-Programmer-Documentation for installation for developing applications.

Installation under Linux

You must add the path to the Speedy-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=.:<Speedy-path>/bin:$PATH

export DBDAT_PATH=<Speedy-path>/include/dat

[…]

See also the Speedy-Database-Programmer-Documentation for installation for developing applications.

Speedy-Database Server Program “speedydb”

Speedy is a main memory database. Nevertheless the database layout and selected user configured tables are stored to disk. The database layout is always stored. Therefore you must specify a directory-path to the place where the Speedy.dat-directory shall be stored. The Speedy.dat-directory contains all information about the database.

There are two ways to specify the path for the Speedy.dat-directory. One is to define the environment-variable SPEEDY_PATH. This path is used if not “speedydb” is started with the “-s”-option which overwrites this setting. If none of these two are used the Speedy.dat-directory 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 Speedy.dat-directory is the current directory (i.e. the directory from which “speedydb” is invoked).

Usage under Windows

You will want to start the database-server on Windows boot-up and stop the database-server on System shut-down. You can do this by adding the two programs “speedydb” and “speedysp” to the autoexec-folder.

If you occasionally want to start the server-program just make an icon on the desktop with program reference to “speedydb.exe”. In this case you will also want to stop the database at a given time. For this you can set up an icon on the desktop with reference to “speedyst.exe”.

Usage under Linux

If you occasionally want to start the server-program just execute “speedydb.exe&” in a command-shell. This command starts the database-server as a background process. You can then stop the database by calling “speedyst.exe” on a command-shell. If you start the server-program in the foreground you can simply press “Ctrl-C” to stop the server-program. In any case all relevant information is automatically stored.

SuSE 7.0-Installation

You can automatically start Speedy when booting Linux and shutdown Speedy when Linux is shut down.

To automatically start Speedy on boot up and stop Speedy on shutdown for the SuSE 7.0-distribution you would have to do the following:

1. Create a file “speedy” with the following content:

#! /bin/sh

# Author: Harald Wabnig

# /sbin/init.d/speedy

. /etc/rc.config

SPEEDY_PATH=<directory path to Speedy-bin-directory>

SPEEDY_DAT=<directory path to Speedy.dat-directory>

# Determine the base and follow a runlevel link name.

base=${0##*/}

link=${base#*[SK][0-9][0-9]}

# Force execution if not called by a runlevel directory.

test $link = $base

# The echo return value for success (defined in /etc/rc.config).

return=$rc_done

case "$1" in

start)

checkproc $SPEEDY_PATH/speedydb && {

killproc $SPEEDY_PATH/speedydb 2> /dev/null

echo -n "Re-"

}

echo -n "Starting database server (Speedy):"

startproc $SPEEDY_PATH/speedydb -s$SPEEDY_DAT || return=$rc_failed

echo -e "$return"

;;

stop)

echo -n "Shutting down database server (Speedy):"

killproc -TERM $SPEEDY_PATH/speedydb || return=$rc_failed

echo -e "$return"

;;

restart)

$0 stop && $0 start || return=$rc_failed

;;

reload)

$0 stop && $0 start || return=$rc_failed

;;

status)

echo -n "Checking for database server (Speedy): "

checkproc $SPEEDY_PATH/speedydb && echo OK || echo No process

;;

echo "Usage: $0 {start|stop|status|restart|reload}"

exit 1

;;

esac

# Inform the caller not only verbosely and set an exit status.

test "$return" = "$rc_done" || exit 1

exit 0

2. Copy file “speedy” to directory '/sbin/init.d'

3. Make soft-links to ../speedy in directory '/sbin/init.d/rc.2'

ln -s ../speedy S60speedy

ln -s ../speedy K60speedy

Command-line Parameters

General format: speedydb [options]

Possible options:

-?: Prints information on command-line options.

-h<HOST>: Defines the host-name to run the database-server. The default value is determined by the system-function "gethostname". Note: The database-server can only be run on the local host. So this parameter can not be used at the moment.

-p<PORT>: Defines the communication port for the database-server. The default value is 12453 (spdycomm.h: DB_DEFPORT).

-m<max-results>: Defines the default maximum number of results from a database select. This value can be changed by the SQL-command "MAX". The default value is 30000 (speedydb.h: DB_MAX_RESULTS).

-s<SPEEDY_PATH>: Defines the path to the directory "Speedy.dat" which holds static database information (i.e. database layout and contents of static tables).

Speedy-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 “speedydb”-command-line option “-m” to overwrite this value or you can use the special SQL-command “MAX <number>” to define another limit)

The maximum number of intermediary results from sub-selects is 1000000

Speedy-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

Speedy-Database Shell Program “speedysh”

The program “speedysh” can be used to basically interact with the SQL-database. You can create databases (e.g. “speedysh CREATE testdb”), destroy databases (e.g. “speedysh DESTROY testdb”), interactively work with database or execute commands from a file (e.g. “speedysh testdb –Isql_file”).

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>.

-i<filename>: The file <filename> is processed instead of keyboard input.

-s|-silent: The typed SQL-commands are not echoed to the output.

-p<PORT>: Defines the communication port for the database-server. The default value is 12453 (spdycomm.h: DB_DEFPORT).

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.

Speedy-Database Stop Program “speedyst”

This program simply sends the STOP DATABASE command to the database server. If no active database server is found a warning is printed. Else the database-server terminates and print status of storing the database and database stopping. This program has no command-line options.

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

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\''