Copyright © 2001, 2002 Harald Wabnig

email: wabnig@testready.net    

homepage: www.testready.net  

 

 

Speedy1-Database User Documentation

 

Speedy1-Database User Documentation. 1

General Advice. 1

Installation. 1

Installation under Windows. 1

Installation under Linux. 2

Speedy1-Database. 2

Speedy1-Database Limitations. 2

Speedy1-Database Shell Program “speedysh1”. 3

Command-line Parameters. 3

Information about Batchfile-Definition. 3

Speedy1 SQL-Interface Language Definition. 3

Lexical parser 3

Datatypes. 4

Syntax of SQL-commands. 6

Special database commands. 7

 

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.