Tcl Database Sort

This Tcl database sort module has the file name dbengsrt.tcl. This module provides database sort facilities for the Bbuuzzb database engine. This database sort API is part of single user database access as well as the database server.

This sort module has the ability to sort a table of any size. The sort order is given as a series of sort specifications. There may be any number of sort specifications given with each specification being in either ascending or descending order.

# Sort Specifications

The list of sort specifications must contain a list (with each single sort specification being delimited by DBENG_LIST_DELIM (defined in dbmess.tcl) of sort specifications. A single sort specification is in the form:

       field_num[,A|D]

The sort specification starts with the field number followed by an optional order (A for ascending, D for descending, not case sensitive). A comma must separate the field number from the sort order. If no order is given, an ascending sort is assumed for that field and the comma is not specified.

# Theory of Operation and Construction Techniques

This module contains two types of procedures:

- db_ - procedures prefixed with db_ are user callable
- dbeng_ - procedures prefixed with dbeng_ are internal

The internal procedures contained in this module should only be called by other procedures in this module. When writing applications that use this API, make your calls to db_ procedures only.

The database table to be sorted is referred to in this document as the source table.

Database sorting works by loading each source table record into memory until a memory threshold is reached. The record contents in memory are then sorted and written to an intermediate (temporary) table called a bin table. When all source records have been read and sorted all the bin tables are then merged.

The record contents in memory, the sort specifications and all the bin tables are managed by three lists. The record contents list is called dbengsrt_rec_list. The sort specification list is called dbengsrt_spec_list and the bin table list is called dbengsrt_bin_list. This module uses the following procedure prefixes to denote procedures which manipulate one of the three lists:

- dbeng_sort_rl_ - dbengsrt_rec_list (the r stands for record)
- dbeng_sort_sl_ - dbengsrt_spec_list (the s stands for spec)
- dbeng_sort_bl_ - dbengsrt_bin_list (the b stands for bin)

Each database table has two dbeng_table array elements that are exclusively used for sorting. The element sort_max_mem controls how much memory should be used by source records before sorting and flushing the contents. The element sort_max_open_bin specifies how many bin tables may be open at any one time. These dbeng_table elements are set to default values (supplied in dbengsrt.tcl) when the table is opened. These thresholds can easily be changed prior to the actual sort (see below).

The sorted table contents always replace the unsorted contents.

The actual sort is accomplished by using the Tcl lsort command. It is quite slow. The sort speed could be increased by re-coding the sort using a different method.

Here is a list of procedures in this module:

# Module Procedures

- db_sort
- db_get_sort_max_mem
- db_set_sort_max_mem
- db_get_sort_max_open_bin
- db_set_sort_max_open_bin
- dbeng_sort_flush_rec
- dbeng_sort_l
- dbeng_sort_compare
- dbeng_sort_write_sort_data
- dbeng_sort_set_final
- dbeng_sort_match_bin
- dbeng_sort_match_tables
- dbeng_sort_read_bin
- dbeng_sort_is_active_input_bin
- dbeng_sort_alloc_bin
- dbeng_sort_sl_load
- dbeng_sort_rl_add
- dbeng_sort_rl_delete
- dbeng_sort_sl_add
- dbeng_sort_bl_delete_all
- dbeng_sort_bl_delete
- dbeng_sort_term

# Module Dependencies

This module is part of single user database access and requires the following modules:

- fio.tcl
- parse.tcl
- logger.tcl
- dbeng.tcl
- dbiocode.tcl
- dbengcfg.tcl
- dbengcat.tcl

# Module Definition Files

The following definition files are required along with this module:

- stdhead.tcl
- dbmess.tcl

# Module Procedures

## db_sort

    Declaration   : proc db_sort {tid specs}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : table tid

          Name    : specs
          Type    : string
    Description   : list of sort specifications

    Returns       : dbeng code

This procedure will sort a table. The list of sort specifications must contain a list (delimited by DBENG_LIST_DELIM) of sort specifications.

The sorted table contents will always replace the unsorted contents.

## db_get_sort_max_mem

    Declaration   : proc db_get_sort_max_mem {tid mem}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : table tid

          Name    : mem
          Type    : positive integer
    Description   : returned max memory

    Returns       : dbeng code

This procedure will obtain the current value of the dbeng_table array element sort_max_mem. Procedure returns the current allowable maximum sort memory in the returned max memory upon success.

## db_set_sort_max_mem

    Declaration   : proc db_set_sort_max_mem {tid mem}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : table tid

          Name    : mem
    Description   : max memory

    Returns       : dbeng code

This procedure will set the current value of the dbeng_table array member sort_max_mem. The acceptable range is greater than or equal to 2,000 bytes.

## db_get_sort_max_open_bin

    Declaration   : proc db_get_sort_max_open_bin {tid open_bin}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : table tid

          Name    : open_bin
          Type    : positive integer
    Description   : returned max open bin tables

    Returns       : dbeng code

This procedure will obtain the current value of the dbeng_table array member sort_max_open_bin. Procedure returns the current allowable maximum number of open bin tables in returned max open bin tables upon success.

## db_set_sort_max_open_bin

    Declaration   : proc db_set_sort_max_open_bin {tid open_bin}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : table tid

          Name    : open_bin
          Type    : positive integer
    Description   : max open bin tables

    Returns       : dbeng code

This procedure will set the current value of the dbeng_table array member sort_max_open_bin. The acceptable range is greater than or equal to 3.

## dbeng_sort_flush_rec

    Declaration   : proc dbeng_sort_flush_rec {}

    Returns       : dbeng code

This procedure will sort and write out the record contents in memory to a bin table.

## dbeng_sort_l

    Declaration   : proc dbeng_sort_ll {}

    Returns       : dbeng code

This procedure will sort the record contents. The record contents list (dbengsrt_rec_list) must have already been loaded.

## dbeng_sort_compare

    Declaration   : proc dbeng_sort_compare {a b}
    Parameters    :
          Name    : a
          Type    : string
    Description   : first record

          Name    : b
          Type    : string
    Description   : second record

    Returns       : <0, 0, >0

This procedure will compare two strings according to the sort specifications. This procedure is used both by the lsort Tcl command to sort the data and by the procedure dbeng_sort_match_tables during the merge process.

## dbeng_sort_write_sort_data

    Declatation   : proc dbeng_sort_write_sort_data {tid}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : table tid

    Returns       : dbeng code

This procedure will write the contents of the sorted record list (dbengsrt_rec_list) to a temporary system table (bin table). The output table is assumed to be open. The output table will be closed (with the dbeng_table array elements retained) once all records have been written.

## dbeng_sort_set_final

    Declaration   : proc dbeng_sort_set_final {tid}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : source table tid

    Returns       : dbeng code

This procedure will take a single bin table and make it the source table.

## dbeng_sort_match_bin

    Declaration   : proc dbeng_sort_match_bin {tid}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : source table tid

    Returns       : dbeng code

This procedure will perform a merge (according to the current sort specifications) of all the bin tables in use (whether they are open or not) and create a single table from all the bin tables.

## dbeng_sort_match_tables

    Declaration   : proc dbeng_sort_match_tables {out_tid}
    Parameters    :
          Name    : out_tid
          Type    : positive integer
    Description   : output table tid

    Returns       : dbeng code

This procedure will consolidate all open bin tables (according to the current sort specifications) into a single table (the output table tid).

## dbeng_sort_read_bin

    Declaration   : proc dbeng_sort_read_bin {tid out_tid}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : table tid

          Name    : out_tid
          Type    : positive integer
    Description   : output table tid

    Returns       : dbeng code

This procedure will read the next record from the table tid. If EOF is encountered, the table will be closed and deleted.

## dbeng_sort_is_active_input_bin

    Declaration   : proc dbeng_sort_is_active_input_bin {tid out_tid}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : bin table tid to test

          Name    : tid
          Type    : positive integer
    Description   : output table tid

    Returns       : dbeng code

This procedure will determine whether the bin table tid to test is open and is not an output bin table.

## dbeng_sort_alloc_bin

    Declaration   : proc dbeng_sort_alloc_bin {bintid}
    Parameters    :
          Name    : bintid
          Type    : positive integer
    Description   : returned tid

    Returns       : dbeng code

This procedure will allocate a new bin member and open a temporary system table for it. A new list member will be added to the end of the bin list (dbengsrt_bin_list). Upon success, the tid of the temp sys table will be returned in the returned tid.

## dbeng_sort_sl_load

    Declaration   : proc dbeng_sort_sl_load {specs}
    Parameters    :
          Name    : specs
          Type    : string
    Description   : list of sort specifications

    Returns       : dbeng code

This procedure will load the list of sort specifications into the sort specification list (dbengsrt_spec_list). Each sort specification in the list of sort specifications is expected to be delimited by DBENG_LIST_DELIM.

## dbeng_sort_rl_add

    Declaration   : proc dbeng_sort_rl_add {tid}
    Parameters    :
          Name    : tid
          Type    : positive integer
    Description   : source table tid

    Returns       : dbeng code

This procedure will add the contents of the current record from the source table tid to the record list (dbengsrt_rec_list).

## dbeng_sort_rl_delete

    Declaration   : proc dbeng_sort_rl_delete {}

This procedure will delete the entire record list (dbengsrt_rec_list) from memory.

## dbeng_sort_sl_add

    Declaration   : proc dbeng_sort_sl_add {spec}
    Parameters    :
          Name    : spec
          Type    : string
    Description   : a single sort specification

    Returns       : dbeng code

This procedure will add a sort specification to the sort specification list (dbengsrt_spec_list).

## dbeng_sort_bl_delete_all

    Declaration   : proc dbeng_sort_bl_delete_all {}

    Returns       : dbeng code

This procedure will delete all elements in the bin table list (dbengsrt_bin_list) and also delete all physical bin files.

## dbeng_sort_bl_delete

    Declaration   : proc dbeng_sort_bl_delete {lbin delete_flag}
    Parameters    :
          Name    : lbin
          Type    : positive integer
    Description   : bin table tid

          Name    : delete_flag
          Type    : boolean flag
    Description   : delete physical table flag

    Returns       : dbeng code

This procedure will delete the bin table tid list entry with or without deleting the physical bin file.

## dbeng_sort_term

    Declaration   : proc dbeng_sort_term {}

This procedure will delete the contents of the sort record, sort specification and bin lists.

Goto Top | Tcl Database Engine and API's | Tcl Applications | Tcl Software Overview | Tcl Library Overview
| Future Lab Home | Contact Webmaster | Feedback

Copyright © 2006 Future Lab, Last Updated Jul 01, 2006