DB-Preparator User Documentation

The DB-Preparator is included in the Speedy- and Speedy1-packages. So download the package appropriate for your System. Packages for Linux (speedy-x.x.x.zip/speedy1-x.x.x.zip) and Windows (speedy_win-x.x.x.zip/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 speedy-x.x.x.zip” under Linux).

Uncompress the package to a new directory. The file-structure contains sub-directories which holds the binaries. There you find the “dbprep” binary which is the executable for the DB-Preparator.

The allowed database-typenames are defined in the “typename.dat”-file. To allow DB-Preparator find this file you must either copy the file into the local directory or set up the DBDAT_PATH environment variable (which is the recommended way).

Note: If you install the Speedy/Speedy1-package you should not need to do any more.

Installation under Windows

To access to the 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>;<binaries-path>\bin;

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

[…]

Installation under Linux

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

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

[…]

DB-Preparator Program “dbprep”

The DB-Preparator is a very powerful facility. Besides the basic database access functions it also allows to generate SELECT commands, compare-functions for table-rows, database-documentation functions and many more.

Command-line Parameters

General format: dbprep <input file> <output-file> [options]

Example: dbprep phonedb.dat phonedb

Possible options:

-db<database-name>: Defines the database-name.

-docu: Generates a HTML-documentation of the database-definition. The name of the documentation-file is ‘<database-name>.htm’. Note: At the moment documentation can only be generated in German!

-NO_DBCACHE: Usually all database-selects are fully evaluated. The results are buffered in the generated functions. If you use this option the results are not buffered. You can use the HOLD-option for single selects to overrule this general option for a single select-command.

-ver<version>: The DB-Preparator allows to define different select-commands for different versions of the program. This options allows you to specify which select-commands should be generated.

Syntax of DB-Preparator Input-Files

The DB-Preparator input-files should get the extension ‘.dat’. The input-file must be in the directory you start the dbprep-program.

The structure of dat-files is as follows:

TABLE/STRUCT-definitions

GROUP-definitions

Table-definitions define database-tables. Each table has a table-name. The name must be defined in the file ‘tablenr.dat’ which should be in the same directory as the dat-file (but you can also define all tables globally in a single ‘tablenr.dat’-file in the DBDAT_PATH-directory). The ‘tablenr.dat’-file simply consists of a list of names and associated unique (table-)numbers; one ‘name number’ per row.

See the application examples on how to define tables/structures and groups.

Generated Standard Functions

<TABLE>_Definition:

extern char * <TABLE>_Definition(void);

Returns the definition of the table.

<TABLE>_Attribs:

extern char * <TABLE>_Attribs(void);

Returns the attribute-names of the table.

<TABLE>_TB_Attribs:

extern char * <TABLE>_TB_Attribs(int kz_db);

Returns the attribute-names of the table considering special naming for documentation.

<TABLE>_Data:

extern char * <TABLE>_Data(DBHANDLE *pDBHANDLE, <TABLE> *<table>);

Returns the data of the row given by <table>.

<TABLE>_IsValid:

extern int <TABLE>_IsValid(DBHANDLE *pDBHANDLE, <TABLE> *<table>);

Checks whether the row given by <table> contains inconsistencies.

<TABLE>_SelectPk:

extern int <TABLE>_SelectPk(<TABLE> *<table>, DBHANDLE *);

Selects the row with matching primary key.

<TABLE>_SelectLk:

extern int <TABLE>_SelectLk(<TABLE> *<table>, DBHANDLE *);

Selects the row with matching logical key.

<TABLE>_Insert:

extern int <TABLE>_Insert(<TABLE> *<table>, DBHANDLE *);

Inserts the row.

<TABLE>_Load:

extern int <TABLE>_Load(<TABLE> *<table>, DBHANDLE *);

Loads the row without checking for unique index.

<TABLE>_Update:

extern int <TABLE>_Update(<TABLE> *<table>, DBHANDLE *);

Updates the row with matching primary key.

<TABLE>_Delete:

extern int <TABLE>_Delete(<TABLE> *<table>, DBHANDLE *);

Deletes the row with matching primary key.

<TABLE>_SelectPk:

extern int <TABLE>_DeleteAll(DBHANDLE *);

Deletes all rows in table <TABLE>.

<TABLE>_SelectPk:

extern int <TABLE>_Do(<TABLE> *<table>, DBHANDLE *);

Executes the SQL-command specified in the iDB-field of the database-table-structure.

<TABLE>_CopyPk:

extern int <TABLE>_CopyPk(<TABLE> *<table>1, <TABLE> *<table>2);

Assigns the primary key of <table>2 to <table>1.

<TABLE>_CmpPk:

extern int <TABLE>_CmpPk(TFSurrogat pkey, <TABLE> *<table>2);

Returns TRUE if the primary key of <table>1 is equal to the primary key of <table>2.

<TABLE>_CmpLk:

extern int <TABLE>_CmpLk(<TABLE> *<table>1, <TABLE> *<table>2);

Returns TRUE if the logical key of <table>1 is equal to the logical key of <table>2.

Example for Using Standard Selects

TABLE TEST SEQ

TEST_Id FSurrogat

Item FLong

TEST_Id

Item

You must specify the SEQ-option in the table header to generate standard select/fetch-functions. The names of the generated functions are <TABLE>_Select and <TABLE>_Fetch.

Example for Using Special Select/Fetch

TABLE TEST

TEST_Id FSurrogat

Item FLong

TEST_Id

Item

SELECT TEST_Select_Item TEST_Cur_Item

"SELECT * FROM TEST " \

"WHERE ITEM = :Item"

FETCH TEST_Fetch_Item

The names of the generated functions are specified in the command (TEST_Select_Item and TEST_Fetch_Item).

Example for Using Special Selectfetch

TABLE TEST

TEST_Id FSurrogat

Item FLong

TEST_Id

Item

SELECTFETCH TEST_Select_Item TEST_Cur_Item

"SELECT * FROM TEST " \

"WHERE ITEM = :Item"

The name of the generated function is specified in the command (TEST_Select_Item).

Note: This kind of select-command can only be used if no more than one result-row can occur.

Example for Using Special Selectcount

TABLE TEST

TEST_Id FSurrogat

Item FLong

TEST_Id

Item

SELECTCOUNT TEST_Count_Item TEST_Cur_Count_Item

"SELECT COUNT(*) FROM TEST " \

"WHERE ITEM = :Item"

The name of the generated function is specified in the command (TEST_Count_Item).

Example for Using Special Selectdata

TABLE TEST

TEST_Id FSurrogat

Item FChar_25

TEST_Id

Item

SELECTDATA TEST_Selectdata_Item TEST_Cur_Selectdata_Item

"SELECT ITEM FROM TEST " \

"WHERE ITEM = :Item"

The name of the generated function is specified in the command (TEST_Selectdata_Item).

Note: This kind of select-command can only be used if no more than one result-row can occur.

Example for Using Special SQL

TABLE TEST

TEST_Id FSurrogat

Item FChar_25

TEST_Id

Item

SQL TEST_SQL_Item

"UPDATE FROM TEST SET ITEM = ‘’ " \

"WHERE ITEM = :Item"

The name of the generated function is specified in the command (TEST_SQL_Item).

Note: This kind of command may not return results.

Example for Using Table-Comparison

TABLE TEST CMP

TEST_Id FSurrogat IGNORE

FK_Id FSurrogat DEFAULT

Item FLong

TEST_Id

Item

You must specify the CMP-option in the table header. Attributes which are marked with the IGNORE-option will not be compared. Attributes which are marked with the DEFAULT-option will just be compared for existence. The name of the generated function is <TABLE>_Compare.

(See DBTest application on how to use the compare-functions.)

Example for Using HTML-Documentation

TABLE TEST

TEST_Id FSurrogat

FK_Id FSurrogat

Item1 FLong (Item 1, 10)

Item2 FLong (20)

TEST_Id

Item1

Items which are marked with a number will be shown in the documentation. If a name is given (e.g. ‘Item 1’) this name will be used for the attribute in the HTML-documentation. The numbers define the sequence in which the attributes are shown in the documentation. The names of the generated functions are <TABLE>_HTML_Attribs and <TABLE>_HTML_Data.

Example for Using Groups

TABLE TEST

TEST_Id FSurrogat

FK_Id FSurrogat

Item FLong

TEST_Id

Item

GROUP Test TESTDAT

TEST

The group Test (which is identified by ‘TESTDAT’) contains one table.

Get_GRPs:

extern void Get_GRPs (_MGRP * pMGrp);

Returns a multiple of all existing groups in the generated file.

Get_Data_Save:

extern int GRP_Data_Save (DBHANDLE * pDBHANDLE, char * fn, char * kuerzel);

Saves the group specified by ‘kuerzel’ to file specified by ‘fn’.

Get_Data_Load:

extern int GRP_Data_Load (DBHANDLE * pDBHANDLE, char * fn, char * kuerzel);

Loads the group specified by ‘kuerzel’ from file specified by ‘fn’.

GRP_Data_Delete:

extern int GRP_Data_Delete (DBHANDLE * pDBHANDLE, char * kuerzel);

Deletes the group specified by ‘kuerzel’.

See the applications for more information.