Creating a table
MakeDB(TableName, Password, CodingKey, StructureDefinition [,Source]) : ErrorCode
The StructureDefinition is the name of a text file, which is built as follows:
The paragraph under [INDEX] is required only, if the table has an AUTO field.
A field specification fieldspec has following structure:
As field identifier all identifiers are acceptable which begin with a letter or an underscore _ and consist of letters, numbers and underscores only. The maximum length is 35 characters.
Following type definitions are possible:
|STRING,Length (length from 1 to 255)
|| String field with the specified maximum length
numerical field according to NumCode
Integers from 0 to 255
Integers from - 32768 to + 32767
Integers from - 2147483648 to + 2147483647
6-byte REAL (old Borland-format)
Floating-point numbers from - 2.9 x 10^39 to +1.7 x 10^38 with 11-12 signed digits
8-byte REAL floating-point numbers (ANSI double)
Floating-point numbers from - 5.0 x 10^324 to +1.7 x 10^308 with 15-16 signed digits
*) tdbengine only, non-compatible with VDP or TurboDatenbank;
**) compatible with all VDP and TurboDatenbank versions;
***) tdbengine and VDP (version 2.5 or higher) only.
Decimal places are taken into account by REAL types only and are regarded for the data transfer with the functions GetField() and SetField() as well as by the export to other data base formats. All REAL types are always stored internally with the optimal accuracy.
If the parameter U is given, a difference is in selection between 0 and "non-defined" values.
Memos and BLOBs
predicates a memo field
The content of a memo field is a reference to a supplementary memo file ("<table>.mmo"). Unformatted texts are stored exclusively in memo files.
BLOB [,block size]
predicates a BLOB field
The contents of a BLOB field represents a reference to an extra BLOB file. Any data may be stored in BLOB files. In the BLOB file the cluster size can be specified by block size (Minimum = 64, Maximum = 8192, Default = 4096). On average, half a cluster is used per entry in the BLOB file, but too small clusters increase the access time.
The counting (in a new table) begins with the start number (or 1, if nothing was specified)
Date and Time
||Unix timestamp (dd.mm.yyyy_hh:mm:ss) (Seconds since 01.01.1970 00:00:00h)
Selection field with the specified value range
Relation field between the given tables.
Attention: The tdbengine does not automatically create the corresponding relation table. For this purpose the function GenRel() is available.
ADL link field for the given table.
It is not tested if the table is present or numbered automatically.
Determining Import Fields
If a source is set for the function MakeDB(), the specified field is replaced with the contents of the given import field of the source. If there is no import field set, the specified field name is used.
If the source file name equals the one set as destination, the table will be accordingly restructured. The character "@" as substitution of the file name.
Note: The structure definition is essentially a machine file created by the user with the help of an assistance program. If you manually compose a structure definition, special attention must be paid to the following:
- The numbering of the fields must be without omissions.
- Field numbers must not appear twice.
- Attach new fields always to the end, since the sequence is not significant.
- If you wish to change a field identifier in the course of a restructuring, don't forget to set the previous identifier as import field, otherwise the fields content gets lost.
The restructuring of a table is always a critical process, as soon as the table contains data already. You can contribute to data security, if you abandon direct restructuring, rename the former table by function RenTable() and set the renamed table as a source. If something is wrong you can still access the former table.
Obtaining the table structure
GetDef(db : REAL; structfile : STRING) : REAL
db : TableHandle of OpenDB()
structdef : Path to a text file, in which the structure definition is described.
Return: 0 = OK, otherwise error code is generated.
The table structure of the opened table db is written to the specified file. Further it can be used again for the creation of new tables. Ramtexts are also possible destinations.
Example:Creating an empty copy of an opened table db.
Deleting a Table
DelDB(TableName, Password : STRING; Code : REAL) : ErrorCode
This function deletes the table including all auxiliary files like Memo, BLOB and index files.
Renaming a Table
RenDB(data name,password,code,new name) : error code
Rename the table with the specified data name and the new name. Memo, BLOB and index files are also renamed if it is necessary.
Opening and Closing Tables
OpenDB(FileName[,Password[,Code[,Mode]]]) : TableNumber
Mode is a supplement to the separate basic modes for denoting the desired access type:
0 : (default value) No changes may be performed in the table.
1 : New records may be created.
2 : Existing records may be overwritten.
4 : Records may be deleted.
8 : Indices may be generated and deleted.
Thus the mode 15 allows all modifications in the table.
Note:If an error appears during the opening of the table, the program is commonly aborted with a corresponding error message. If the internal error processing was disabled, the function return value is 0 and the error can be obtained by TDB_ErrorCode.
VAR d : REAL
IF d:=OpenDB("database/addresses.dat")=0 THEN
CGIWriteHTML("By opening the table following error is appeared: ")
CGIWriteHTML(Str(FileSize(d))+" Data blocks are available ")
Unlike the TurboDatenbank under DOS, the tdbengine doesn't automatically open the table that are referenced by L- or R-fields. Those must be separately opened by using the function OpenDB(), if necessary. As this takes place, it should be mentioned that the last opened table is always put in the foregoing relation structure. For example, this means that relation tables should always be opened AFTER the tables only, between they are linked. If a table with a L-field is opened, the corresponding linked table must be already opened in order to actuate the linking. If this can't be ensured the use of the RELATION command is strongly recommended.
An opened table can be closed again at any time by CloseDB().
At the end of the program the function CloseDB() is automatically executed for all opened tables.
Note:In the context of a good programming style you must close again all tables, which were opened by your program.
Obtaining Table Size
FileSize(table number[,mode] : INTEGER) : INTEGER
This function returns the number of records (= rows) in the table.
Mode = 0 (preset value) standard -> The number of records in the corresponding .dat (or -1 by full-text index word lists).
Mode =1 extended ->
The number of the ID entries in the ID index by index word lists, which were created by function ScanRecs() and Mode=4.
The number of the entries in the IN2 index by REL files, which were created by function ScanRecs() in the Mode=4.
Reading, Writing and Deleting Records
The tdbengine provides a record buffer for each table. All functions that process data fields do this to the internal record buffer.
ReadRec(TableNumber,RecordNumber) : INTEGER
Reads the record with the record number in the record buffer of the table with the given table number (handle). When the record number is 0, an empty record is provided (the buffer gets cleared). The return value of ReadRec() is the record number if the record can be read, otherwise it is 0.
When returning an illegal record number a runtime error is generated.
WriteRec(TableNumber, RecordNumber) : INTEGER
Writes the current record buffer into the table right at the position of the given RecordNumber. Allowed values for RecordNumber are 1 to FileSize(TableNumber) + 1.
Providing illegal record numbers a runtime error is generated.
If the RecordNumber is between 1 and FileSize(), the corresponding record will be overwritten, otherwise the record is appended to the table.
DelRec(TableNumber, RecordNumber) : INTEGER
Deletes the record with the given RecordNumber. By deleting a record the tdbengine does the following:
- The record is deleted from all indexes of the table.
- If this is the last record of the table, the table will be shortened by one record.
- Otherwise the last entry of the table is copied to the position of the record, which is to be deleted; all indexes are correspondingly updated and finally the table is shortened by one record.
This manner of action has the advantage that the tables always include up-to-date records only. No less care should be exercised, when functions are referred to physical record numbers. Although the function RecNo() gives always the current record numbers (even if these are shifted due to deletions). While using CGI-programs, attention should be drawn to the fact that the connection may be interrupted at any time, and the information is transmitted by CGI-variables on the next call. The reference to physical record numbers can have fatal consequences here, because they could already have been changed between one call and another.
A solution to this problem was developed using the reference to the auto-numbers of the table instead. Thats why the tdbengine makes two new functions available, namely AutoField() and FindAuto(), which incredibly simplify the conversion of record numbers and auto-numbers.
AutoField(TableNumber) : INTEGER
This function determines the number of the AUTO-field (or 0, in case there is no AUTO field in the table).
The function GetRField(table number,AutoField(table number)) obtains the Auto-number of a record. That's what this function is for:
AutoRecNo(db : INTEGER) : INTEGER
db : TableHandle returned by OpenDB()
RecNo(db) if there is no AUTO field in db
FindAuto(db,x : REAL) : REAL
db : TableHandle of OpenDB
x : (Auto-)number
x if there is no AUTO field in db
Whenever it is possible (that's if an auto-number is present in the table), the information should be transferred using an auto-number as a record-identification carrier between CGI-programs.
Another problem is the use of full-text indices, which are also referring to physical record numbers. A full-text index is not automatically updated, when a record is deleted. It is not possible at all, because full-text indices are not a component of the table and thats why the tdbengine can not have information about it.
A way out of this difficulty is to recreate a full-text index after every deletion in a table. On small tables this manner of action can be thoroughly put into practice due to high processing speed of the tdbengine. The other, more expensive and also more elegant way out is to keep the full-text index over the table dynamically. Having functions like ScanRec() and UnScanRec() the required functions are available. Here you can see an outline for the deletion of a record including further full-text indexing afterwards:
PROCEDURE DelRecWithFullText(TheTableHdl, TheRecNo : REAL)
ReadRec(TheTableHdl,TheRecNo) // Position on desired record
UnScanRec(...) // Delete full-text index for this record
ReadRec(TheTableHdl,FileSize(TheTableHdl)) // Position of last record
UnScanRec(...) // Delete full-text index for the last record
DelRec(TheTableHandle,TheRecNo) // Last record is inserted into here
ScanRec(...) // AND full-text is indexed anew
Searching and marking records
The access to data fields
GetField(TableNumber, Field) : STRING
GetRField(TableNumber, Field) : REAL
SetField(TableNumber, Field) : REAL
SetRField(TableNumber, Field) : REAL