Introduction

The package Qgdbm is wrapped around Tclgdbm and provides a more convenient way to store and retrieve data. It is however not a replacement for a "professional" database. But serves well for applications which simply have to store a small/medium amount of data which fits into a simple table-structure.

With Qgdbm you can access information from gdbm-files in an SQL-like fashion. Not really SQL, but a more Tcl-adopted SQL, let's name it TSQL for (Tcl or Tiny(?)-SQL).

Qgdbm stores each table in a separate file named <table-name>.qg. All tables are stored in a specified root-directory.
All Qgdbm-Table-files have one reserved key/value-pair with key "@". This entry is used to store administrative information like table-structure, number of inserts, date of creation, ...
Because all needed information about one table is stored in the table-file itself it is easy to copy this file elsewhere, without having to worry about system-information or loss of information.
You simply copy the table-file you need, you can even copy into another user-directory (see below).

The system-tables (or exactly in version 0.3 the one system-table named system.qg) holds information about users. You can create tables which belong to a specified user (just like in "normal" databases). In this case no rights are given to different users (maybe this will come in one of the next versions of Qgdbm), so the paradigma is everyone sees everything.

Each user has his own directory (directly under the root-directory). Tables could be specified as <user>.<table>, like the way you access a different scheme (e.g. in Oracle).
Managing users is only possible in the "With-System"-Option.

Properties

• SQL-Like access to database-files
• Tables with datatypes and constraints
• Exactly one primary key on each table
• Different users/schemes
• Optional logging-mechanism
• Table-locking
• Values of arbitrary length
• Platform-independent

• Transactions
• Permissions on Tables (like grants)
• Quotas/Tablespaces...
• Multiple primary keys, foreign keys

Let's take a look at some simple examples:

1. Simple Example

package require qgdbm
qgdbm::init -rootdir /tmp/

qgdbm::tsql create table address \
  {{name char} {street char} {zip_code integer} {city char}}

qgdbm::tsql insert into address {$name $street} values \
    [list {Grobi Sesame-Street}]
qgdbm::tsql insert into address values \
    [list {Ernie Sesame-Street 12234 Sesame}]

set result [qgdbm::tsql select * from address]
qgdbm::cleanup

2. Simple Example

package require qgdbm
qgdbm::init -rootdir /tmp/ -system 1

qgdbm::tsql create user grobi identified by grobis_password
qgdbm::tsql create table grobi.cookies \
    {{id integer} {name char} {amount integer}}

qgdbm::cleanup

Commands

• qgdbm::init/qgdbm::cleanup (Initialization/Cleanup)
• qgdbm::tsql ALTER TABLE
• qgdbm::tsql ALTER USER
• qgdbm::tsql CREATE TABLE
• qgdbm::tsql CREATE USER
• qgdbm::tsql DROP TABLE
• qgdbm::tsql DROP USER
• qgdbm::tsql DELETE
• qgdbm::tsql INSERT
• qgdbm::tsql SELECT
• qgdbm::tsql UPDATE

• qgdbm::descTable
• qgdbm::gdbmHandle
• qgdbm::forceReorg
• qgdbm::headerField
• qgdbm::log

qgdbm::init/qgdbm::cleanup (Initialization/Cleanup)

qgdbm::tsql ALTER TABLE

qgdbm::tsql ALTER USER

qgdbm::tsql CREATE TABLE

qgdbm::tsql CREATE USER

qgdbm::tsql DROP TABLE

qgdbm::tsql DROP USER

qgdbm::tsql DELETE

qgdbm::tsql INSERT

qgdbm::tsql SELECT

qgdbm::tsql UPDATE

qgdbm::descTable

% qgdbm::descTable address
Current working directory: /usr/svogel/tclgdbm/tests
Current gdbm-handle      : gdbm28
DBFile : address.qg (Version: 0.3)
PK     : name (char)
Fields : street (char)
Fields : zip_code (integer)
Fields : city (char) constraints: '[string length $city]'
Size   : 4
Created: 02/12/00 19:09:10

Statistics:
No insert: 3
No select: 9
No update: 0
No delete: 0

qgdbm::gdbmHandle

qgdbm::forceReorg

qgdbm::headerField

qgdbm::log

Examples

Example #1: Create a table

qgdbm::tsql create table address \
    {{name char} \
     {street char} \
     {zip_code integer} \
     {city char {[string length $city]}}}

Example #2: Insert values into a table

set data {Ernie    sesame-street  12345 Sesame
          Clown  Whitehouse-Av. 9999  Washington
          Quichote Windmillstreet 455   {Don't know where}}

foreach {name street zip city} $data {
  qgdbm::tsql insert into address {$name $street $zip_code $city} \
    values [list [list $name $street $zip $city]]
}

set data {{Ernie    sesame-street  12345 Sesame}
          {Clown    Whitehouse-Av. 9999  Washington}
          {Quichote Windmillstreet 455   {Don't know where}}}
qgdbm::tsql insert into address values $data

qgdbm::tsql insert into address {$name $city} values {{Bert Sesame}}

qgdbm::tsql insert into address {$name $street} values \
    {{Bert sesame-street}}

Column 'city' with value '' doesn't fulfill constraint
'[string length $city]'.

Example #3: Select values from a table

The following statement updates all rows in address:

qgdbm::tsql update address {$city} Berlin

Performance

You may ask it Qgdbm fast enough, because it is based on Tcl. The answer is that depends (as always). Comparing it with a "real" database is a bit unfair, but let's compare it with pure Tclgdbm. Because Qgdbm is based on Tclgdbm, we can see the overhead added to Tclgdbm to make it easier to use.
The times are taken under Linux on a Intel Pentium 133-Computer with Tcl 8.2.
Figure 1: Measured time for Inserts in Tclgdbm and Qgdbm For the first test 1000 rows were inserted. Each row was about 250, 500 or 1000 Bytes (which makes a total of nearly 250, 500, 1000 KB, to be exact 244, 488, 976 KB).
Inserting nearly 1 MB with Qgdbm (and 1000 qgdbm::tsql insert ...-operations) is slow, it takes 8.4 seconds. Inserting these values with one insert is much faster (4 secs). But surely Tclgdbm cannot be beaten, it takes 0.7 seconds.
Qgdbm is around 10-15 times slower than Tclgdbm.
In fact, inserting 1 MB in one chunk is not really an all-day situation. Qgdbm is surely not a tool for time-critical applications, but in applications with user-interaction and a small/medium amount of data it is worth the overhead.
Let's have a look at the time for selects.
Figure 2: Measured time for Selects in Tclgdbm and Qgdbm From the database-file with 1000 rows, we select 100 rows (Again each row has 250, 500 or 1000 Bytes (in total nearly 25, 50, 100 KB are returned from the select-statement).
Again, Tclgdbm is really fast (under 50 milliseconds). When running a select-statement with a where-expression in Qgdbm the time is between 1 to 1.4 seconds. This is because in case of an unspecific where-expression a full-table-scan is done.
Selecting the same values with the pklist-parameter makes Qgdbm much faster (between 90 and 160 milliseconds).
By the way when doing a full-table-scan on a table with 500 rows (instead of 1000) the time is half as much (around 0.5 to 0.7 seconds).
Whenever possible you should use the parameter pklist in the select-statement.
Further you should limit your table-size to a maximum of 1000 rows.

---

Last changed: 24. Feb 2000 21:00 (MET)
Created: 08. Feb 2000

For Suggestions, improvements, bugs or bug-fixes feel free to contact:

Stefan Vogel (stefan.vogel@avinci.de)