Qgdbm for Tgdbm (Version 0.5)

<table>

The name for a table (can contain only characters and numbers [a-zA-Z0-9_]) and is either user-unspecific (e.g. address) or in a user-scheme (e.g. ernie.address).

Because the tablename directly maps to a filename, the tablename is converted to lower characters (no conflicts between Windows or Unix filename-conventions).

<coln>

Name of the n-th column in a table. Valid characters are [a-zA-Z0-9_].

The columns are case-sensitive. But I suggest using either uppercase or lowercase columnnames, because you have to write the corresponding column-variables exactly as you defined them.

<dattypn>

The (n-th) datatype of a column.

One of the following possible datatypes for a column:

char – strings of arbitrary-length, where arbitrary really means arbitrary. I didn’t check for a maximum length. Is there a limit for Tcl?

integer – not to explain

real – floating-point numbers (e.g.: 3.141)

date – the internal tcl-representation of date/time, that is: integer (e.g.: [clock seconds])

Datatype are case insensitive, so you can even mix upper- and lowercase in create- or alter -table statements.

These datatypes are used when a sorting-column is provided in a select-statement (the datatype is used to pass it to the tcl lsort command to provide the right sorting-criterium). Or when inserting or updating is done.

<conn>

A constraint for the n-th column.

A valid tcl-expression which is used to check if a value inserted or updated is valid. Be careful with quoting when specifying constraints.

<username>, <password>

The name of a user (valid characters are [a-zA-Z0-9_]) and password (all characters are valid). The password is stored unencrypted in the database. This is not a safety-leak, because we use “everyone sees everything”, anyhow.

The username is used to create a directory directly under root-dir. To avoid conflicts in using Qgdbm under Windows/Unix, … the username is converted to lower characters. So the username is case-insensitiv.

where <expr>

The where-condition in Qgdbm is realized via the Tcl-expression mechanism. The columns are specified like “normal” values of Tcl-Variables. If you have the table address (see Simple Example), you could search for an entry like this:

qgdbm::tsql select * from address where {"$name" == "Ernie"}

In the where-expression you can almost specify anything (even your own functions), e.g.:

qgdbm::tsql select * from address \
  where {[string match "Washington" $city] \
         || ("[string tolower $name]" == "clown")}

Be sure to do the quoting right, because the where-expression is first substituted (to fill in the variable-values you specified) and then send to expr.

pklist <pklist>

In most cases you would like to access the data stored in Qgdbm with the primary key (this must always be the
first column you specified in the create table-statement). To simplify the parsing of tsql-commands you have to give these primary keys explizitly to qgdbm::tsql. This is done with the parameter pklist.

When you do not tell qgdbm:tsql the keys you want to search for with the parameter pklist a full-table-scan is done. pklist is a “normal” tcl-list, e.g.

qgdbm::tsql select * from address pklist {Ernie Clown}

If both pklist and where-expr are given to qgdb::tsql [select|delete|update] the given list of primary keys are searched for a matching where-expr. That means pklist and where-expr are combined via AND.

<Null>

This is not the normal database NULL-value (or not value). This is simply an empty string. There is no distinction between no-value and empty-value.

Syntax:

 qgdbm::init
  -root <rootname> ''Default system''
  -rootdir <root-directory> ''Default ""''
  -hd <header-key> ''Default @''
  -dbext <database-file-extension> ''Default qd''
  -system <with-system-or-not> ''Default 0''
  -log <with-loggin-or-not> ''Default 0''
  -reorganize <number-of-deleted-records> ''Default 300''
  -help

 qgdbm::cleanup

Return-Value:

When qgdbm::init is called with -help the values of all parameters are returned.

Description:

With qgdbm::init the Qgdbm-Database is initialized. If you forget the call to qgdbm::init strange errors might happen.

The parameters have the following meaning:

-dbext: The Qgdbm-database-file-extension. The default is “.qg”. This should be changed only for a very good reason.

-root: The name of the root-table (if specified with -system 1; useless otherwise) without extension (-dbext). Not to be changed, too.

-rootdir: The place of the database. Under this directory the system-table-file and the user-directories will be created. With this option you can have different databases laying around. With a simple call to qgdbm::init you switch to another database.

-hd: The key of the admin-row in each table. The default is @. In case you need a key with this symbol you may set the header-key to something different. But remember you have
to do this for the whole database and each time you initialize it or else nothing will work. It is a good idea to leave this value untouched.

-system: 0 or 1. With -system 1 a system-table is created (if it doesn’t exists). If you want to create different users you have to initialize with -system 1. Else (the default -system 0) no users could be created and all tables are created directly under rootdir.

-log: 0 (default) or 1. If Qgdbm gets initialized with -log 1 a log-table named “log.qg” is
created. Nearly all commands select, delete, ... will leave an entry in this log-table. This option is provided for debugging-purposes and should not be used unless you really want to slow down the database-operations.

-reorganize 300 (default). When data is deleted from gdbm the space is not freed until a call to reorganize is made. With this option you can configure when to reoganize the table-files. It defaults to 300 (that is after 300 deletes a call to reorganize is automatically made, thereby shrinking the file-size to the minimum needed). Depending on the size of data-rows and the file-size you are willing to accept this can be set to a higher or lower value. With -reorganize 0 or

-reorganize "" no automatically reorganization is done.

Be sure to call qgdbm::cleanup when you are finished with your database-operations, because Qgdbm keeps the table-files
opened unless told to close them (with cleanup). All the opened gdbm-handles are closed and the lock on the
tables are removed.

Syntax:

qgdbm::tsql alter table <table>	add \
    {{<col1> <dattyp1> [<con1>]} \
	{<col2> <dattyp2> [<con2>]} ...}
qgdbm::tsql alter table <table> modify \
	{{<col1> <dattyp1>

	[<con_1>]} {...} ...}
qgdbm::tsql alter table drop {<col1> <col2> ...}
qgdbm::tsql alter table rename {<col1> <col1_newvalue>} \
	{<col2>...}...}

Return-Value:

None

Description:

These commands are used to modify the columns of a table. With drop all the named columns are removed (including all data for these columns!). You cannot remove the primary key-column!

modify, changes the datatypes and constraints of the given columns. A check is made if all values are valid in regard for the new datatype and constraint. If not an error is thrown.

With add columns can be added to the table. <Null>-Values are inserted for these columns.

rename renames the columnnames of the listed columns to the given new names.

Example:

 qgdbm::tsql alter table address add column [list {houseno char}]
 alter table address drop column city

Syntax:

 qgdbm::tsql create table <table> \
    {{<col1> <dattyp1> [<con1>]} \
    {<col2> <dattyp2> [<con2>]} ...}

Return-Value:

None

Description:

Create a table with the given columns and their corresponding column-datatypes and constraints. Constraints are optional. But remember the first column is always the primary key and this column is never optional.

A constraint is defined like the where-expr in a select-statement. The corresponding column is provided in a variable with the name of the column.

When an error occurs while inserting multiple rows at one time no row will be inserted.

When you specify a row with a primary key that’s already in the database an error is thrown and no value is inserted.

When a table is created a file is generated in the root-directory or in the specified user-directoy named “<table>.qg”. (See also description of <table>-conventions)

You are searching for an aequivalent to the standard SQL:

create table xyz as select * from abc (that is create a new table xyz which has exactly the same structure and the same data as abc)?

You don’t need a tsql-statement for that. Simply copy the file abc.qg to xyz.qg. That’s one of the benefits of using one file for each table.

Rename a table? Simply rename the corresponding file. But be sure to have closen your Tcl-application that uses this table!

Example:

To create a column with a NOT NULL-Constraint is simple:

qgdbm::tsql create table person \
    {{persnr integer} \
    {last_name char {[string length $last_name]}} \
	{first_name char} \
    {salary integer}}

Be sure to quote the constraint correctly, because the constraint is substituted with subst before it is evaluated with
expr.

For further examples see Examples.

Syntax:

qgdbm::tsql delete from <table> [where <expr>] [pklist <pklist>]

Return-Value:

None

Description:

Deletes the specified rows from table <table>. The where-expr and the pklist are optional.

If no where-condition or pklist is specified all rows will be deleted.

If both are given, the given list of primary keys (pklist) will be searched for a matching where-condition

Example:

qgdbm::tsql delete from grobi.cookies deletes everything

qgdbm::tsql delete from address where {"$street" == "sesame-street"}

or better because the quoting is less confusing:

qgdbm::tsql delete from address \
    where {[string equal $street "sesame-street"}

	qgdbm::tsql delete from address pklist {Ernie Clown}

Syntax:

 qgdbm::tsql insert into <table> {<col1> <col2> ...} \
    values {{<val11> >val12> ...} \
    {<val21> <val22> ...} ...}

 qgdbm::tsql insert into <table> \
    values {{<val11> <val12> ...} \
    {<val21> <val22> ...} ...}

Return-Value:

None

Description:

If you insert values in the Qgdbm-Database be sure to always give the primary key-column (the first column in
the table-definition). The columns are specified as usual via the "value"-notation with a $-prefix.

(You could alternatively give the columns without $; The $-prefix is only for consistency)

If you do not call insert with the column-parameter, you have to give values for all columns of the table or else an
error is thrown.

The values are provided as a list of lists. Therefore you can use the result of a select-statement directly to insert.
This implies that you have to make an additional list, if you want to insert only one row.

Example:

See this Simple Example.

qgdm::tsql insert into address_new {$name $street} \
    values [qgdbm::tsql select {$name $street} from address]

Syntax:

 qgdbm::tsql select {<col1> <col2> ...} from <table> \
    [where <expr>] [pklist <pklist>] \
    [order_asc|order_desc <coln>]
 qgdbm::tsql select * from <table> \
    [where <expr>] [pklist <pklist>] \
    [order_asc|order_desc <coln>]

Return-Value:

List of selected and possibly ordered values

Description:

Select all columns of all rows which are given in col1 col2... (or all if *) and which match the where-expr and/or pklist.

The where and pklist are optional.

Columns have to be provided in $-notation.

You are not limited to specify column-names in the select-statement as in “real” SQL you could also provide
constants (see Examples). The result is a list of lists (that is a list of rows with a list of column-values).

Be sure to use the parameter pklist as often as possible, because without this parameter always a full-table-scan is done.

If the order-criterium is provided the values would be order by the given column ascending or descending. Only one column could be provided.

The $-prefix of the order-column is optional. In fact the $ is removed from the order-column, but the syntax is provided for consistency with the other columns.

Example:

See the example-section.

Select the system-date and the names of table address with lowercase-characters:

qgdbm::tsql select {[string toupper $name] [clock seconds]} from address

Want a row-counter in the selected fields? Simply put your code into the select-statement:

set i 0
qgdbm::tsql select {[uplevel #0 {incr i}] $last_name,$first_name} \
    from person

But be careful not to mess with the column-variables.

Syntax:

 qgdbm::tsql update <table> {<col1> <col2>...} \
    {<val1> <val2>...} \
    [where <expr>] [pklist <pklist>]

Return-Value:

None

Description:

Update the given columns of the affected rows with specified values. The affected rows are determined the same way as in the select or delete-statement.

Keys given in pklist, which are not found in the table are silently ignored.

The given columns (again given with $) are updated with their corresponding values.

For the update-statement you need not specify the columns prefixed with $. The $-notation is only
provided for conistency. For convenience you could give the column-names only (this avoids a little bit of quoting, too).

Example:

See example-section

Syntax:

qgdbm::descTable <table>

Return-Value:

None

Description:

This prints a description of the given table to stdout equal to a describe <tablename> in Oracle-SQL.

This is provided as a convenience-function, so you don’t have to mess with the table-header stored in key @.

The output (see example) gives the current working directory (this is needed for the determination of the table-file), the associated
gdbm-handle, the corresponding database file (joined with the rootdir specified in the initialization-call
qgdbm::init), the primary key (PK) and the other fields with its names (the datatype) and possible constraints.

Furthermore the number of entries is given (Size), remember that the header element is always counted, so the actual number of
entries is (size – 1).

The date of creation of this table is printed with some primitive statistics of the table: number of insert, selects, updates and deletes.

Example:

% qgdbm::descTable address
Current working directory: /usr/svogel/tgdbm/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

Syntax:

qgdbm::gdbmHandle <table>

Return-Value:

Corresponding gdbm-handle to the given table

Description:

In case you want to access the table-file directly with the commands from Tgdbm, you can retrieve the gdbm-handle with this command.

Example:

To determine the number of entries:

set size [[qgdbm::qgdbmHandle |address] count]

Remember that this counts the header-key @. So the real size of data entries is [expr $size -1]

Syntax:

qgdbm::headerField <table> <field>

Return-Value:

Value of field <field> in header of <table>

Description:

To access the system-information stored in each table you can read the different fields stored in key
@ with this command.

The currently defined fields are:

version: Versionnumber of Qgdbm (should be 0.3).

createdate: The date of creation of the file as the internal tcl-clock-value.

fields: A list of field/column-names where the first listelement is the primary key.

types: A list of datatypes of the columns in the same order as in fields.

constrs: A list of constraints for the columns.

no_insert: The number of inserts.

no_update: The number of updates.

no_delete: The number of deletes.

no_update: The number of updates.

Example:n

qgdbm::headerField address fields

returns: name street zip_code city

Syntax:

qgdbm::log command table time number_of_rows

Return-Value:

None

Description:

This command only works when qgdbm::init -log 1 is called for initialization.

log is used internally for time-measurement but you can use it for logging-purposes, too. The format of the log-table is:

id: primary key, a simple id (which is calculated in log.

command: The column to hold the command-string (or any other string you provide as command).

table: The table name for which the command is executed.

time: The time in microseconds (as returned by time) needed for command.

number_of_rows: The number of rows affected by this command.

You can use this command to log your own messages. But because logging is only enabled with -log 1 there is a lot of overhead done for logging, which slows down Qgdbm. So defining your own log-table and your own log-command would be much better.

Example:

qgdbm::log select address 0 1