Deutsch English
Home
About tdbengine
Newsletter
Download
Helpware
Forum
Chat
Documentation
Installation
Configuration
First Steps
Runtime Switches
Text files
System functions
Database functions
Index functions
Full-text Indexing
Memos and BLOBs
Semaphore concept
Error codes
Hints for PHP developers
Basic Course
Function reference
HOWTO...?
Projects
Links
Benchmarks
Bug Reporting
Support request
 
Home    Overview    Search    Impressum    Contact    Members
Database functions

Creating a table


MakeDB(TableName, Password, CodingKey, StructureDefinition [,Source]) : ErrorCode

The StructureDefinition is the name of a text file, which is built as follows:

[STRUCTURE]
field_1=fieldspec
field_2=fieldspec
...
[INDEX]
id=indexdef
index_1=ind-name:ind-desc
index_2=ind-name:ind-desc
...

The paragraph under [INDEX] is required only, if the table has an AUTO field.

A field specification fieldspec has following structure:

FieldIdentificator,TypeDefinition[,(ImportField)]

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:

Strings:

STRING,Length (length from 1 to 255) String field with the specified maximum length

Numbers:

NUMBER,NumCode[,decimal places][,U]

numerical field according to NumCode



NumCode Type Value range
1

byte

Integers from 0 to 255

2

16-bit integer

Integers from - 32768 to + 32767

4*

32-bit integer

Integers from - 2147483648 to + 2147483647

6**

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

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

MEMO

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.

Auto increment

AUTO[,StartNumber]

Auto-NumberField (AUTOINCREMENT)

The counting (in a new table) begins with the start number (or 1, if nothing was specified)

Date and Time

DATE DateField (dd.mm.yyyy)
TIME TimeField (hh:mm)
UTIME Unix timestamp (dd.mm.yyyy_hh:mm:ss) (Seconds since 01.01.1970 00:00:00h)

Enumeration

SELECT,Value1,Value2,Value3,...

Selection field with the specified value range

Relations

REL

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.

LINK,Table

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.

GetDef(db,'ramtext:text:structure')
MakeDB('copy_of'+DBName(db),'',0,'ramtext:text:structure')


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.

Example:

VAR d : REAL
.EC 1
IF d:=OpenDB("database/addresses.dat")=0 THEN
  CGIWriteHTML("By opening the table following error is appeared: ")
  CGIWriteHTML(TDB_ErrorStr(TDB_LastError))
ELSE
  CGIWriteHTML(Str(FileSize(d))+" Data blocks are available ")
END

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.

CloseDB(TableNumber)

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()

Return value:

RecNo(db)    if there is no AUTO field in db
GetRField(db,AutoField(db))  otherwise

FindAuto(db,x : REAL) : REAL

db : TableHandle of OpenDB
x : (Auto-)number

Return value:

x   if there is no AUTO field in db
FindRec(db,Str(x),0,1)  otherwise

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
  IF TheRecNo<=FileSize(TheTableHdl)
    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
  ELSE
    DelRec(TheTableHdl,TheRecNo)
  END
ENDPROC


Searching and marking records

FindRec()
FirstRec()
LastRec()
NextRec()
FindAndMark()

The access to data fields

GetField(TableNumber, Field) : STRING
GetRField(TableNumber, Field) : REAL
SetField(TableNumber, Field) : REAL
SetRField(TableNumber, Field) : REAL

Marking functions

GetMarks
PutMarks
FirstMark
NextMark
DelMarks



tdbengine Anwendungen im Web:

Open-Source Web CMS


Open-Source Bug-Tracking


Free wiki hosting

Open-Source Wiki-System

Kostenloses Foren-Hosting

Dišt mit tdbengine 8-)

tdbengine chat
irc.tdbengine.org
#tdbengine

   Copyright © 2003-2004 tdb Software Service GmbH
   Alle rechte vorbehalten. / All rights reserved
   Last changed: 05.05.2004


ranking-charts.de

Programmers Heaven - Where programmers go!