Integrated Database Details

Working with Database Tables

The "Current Record"?

Changing the Current Record

Accessing a Field in the Current Table Record

Updating Field contents in the Current Table Record

Accessing a Table Field Using an Expression

The EXTRACT / END EXTRACT Block

Iterating Through the Collection

Record Locking and Concurrency

When a table is opened for input, records are implicitly unlocked as they become current.

When a table is opened for update or output, records are implicitly locked whenever a field value is modified.

In cases where a field is being incremented or otherwise read and written in the same operation, it is recommended to explicitly lock the table first using lock table. This ensures correct behavior in concurrent environments.

Implicit Writes and Unlocking

Whenever a new record is made current, any previously locked records are virtually written to storage and unlocked.

Modified records are physically written to storage using lazy writes, allowing updates to be batched efficiently.

Explicit Unlocking and Commit Control

To explicitly unlock a table, use:

To unlock all tables, use:

To flush all virtual writes for a table and commit them to storage, use:

To commit all writes for all tables, use:

Key Lookup Methods in Sheerpower

Sheerpower provides four main methods for key lookups in tables:

After any key lookup or extract operation, _extracted contains the number of matching records found.

1. Exact Key Lookup:
   • Uses the set table statement to specify an exact key value for retrieval. _extracted will be zero if no record was found; otherwise it will be one.
   • set table is intended for lookups that expect at most one matching record.
   • Syntax:
     set table table_name, field key_field: key key_value
   • Example:
     set table customers, field customer_id: key 'C123' if _extracted > 0 then print "Customer found: "; customers(name) else print "Customer not found." end if
2. Exact Key Lookup to Find All Duplicates:
   • Uses the extract table statement with the key clause to specify a key value for retrieval.
   • Syntax:
     extract table table_name, field key_field: key key_value$
   • Example:
     extract table products, field product_code: key 'A1'
       include products(in_stock) > 0
       sort by products(product_code)
     end extract
     for each products
       print products(product_code); " - "; products(name)
     next products
3. Partial Key Lookup:
   • Uses the extract table statement with the partial key clause to specify a partial key value for retrieval.
   • Syntax:
     extract table table_name, field key_field: partial key partial_key_value$
   • Example:
     extract table products, field product_code: partial key 'A1'
       include products(in_stock) > 0
       sort by products(product_code)
     end extract
     for each products
       print products(product_code); " - "; products(name)
     next products
4. Key Range Lookup:
   • Uses the extract table statement with the from ... to ... clause to specify a range of key values for retrieval.
   • Syntax:
     extract table table_name, field key_field: from start_key_value$ to end_key_value$
   • Example:
     extract table orders, field order_date: from '2023-01-01' to '2023-12-31'
       sort by orders(order_date)
     end extract
     for each orders
       print orders(order_date); " - "; orders(total)
     next orders

Sheerpower Extract Block with Examples

exclude logical expression

The exclude statement is used to exclude records from the extract block that match the logical expression nn.

Example:

This example excludes employees who are under 18 years old and those who have an inactive status from the extract block.

include logical expression

The include statement is used to include only those records that match the logical expression nn.

Example:

This example includes only sales records from the North region with an amount greater than $1000.

exit extract

The exit extract statement is used to exit the extract block and with the collected records up to that point.

Example:

This example exits the extract block if an order date is before January 1, 2023, and processes the collected records.

cancel extract

The cancel extract statement is used to cancel the entire extract block without collecting any records.

Example:

This example cancels the extract block if any item has a quantity of 0, resulting in no records being processed.

sort by xxx

The sort by xxx statement is used to sort the records in ascending order based on the expression xxx.

Example:

This example sorts the customer records by their last names in ascending order.

sort descending by xxx

The sort descending by xxx statement is used to sort the records in descending order based on the expression xxx.

Example:

This example sorts the product records by their prices in descending order.

Explanation

The extract table block creates a collection of table records. When the EXTRACT BLOCK is exited, the special variable _extracted contains the number of records in the most recent extract. Typically after an extract block is a FOR EACH block that iterates through the collection. If the EXTRACT BLOCK contained any SORT statements, the sorting is deferred until the FOR EACH block is executed. This enables dramatic performance improvements if later using the reextract or append options.

Closing Tables in Sheerpower

In Sheerpower, after you've finished working with a table, it's important to properly close it to free up system resources and ensure that all data operations are completed correctly. The correct syntax for closing a table is:

Example Usage

1. Opening a Table:

   First, you open the table using the open table command.

   open table payroll: name '@..\data\payroll'
2. Performing Operations:

   You can perform various operations like extracting, filtering, sorting, and iterating over records.

   extract table payroll include payroll(salary) >= 100000 sort descending by payroll(salary) end extract for each payroll print payroll(name); ", "; payroll(salary) next payroll
3. Closing the Table:

   Once you've completed your operations, use the close table statement to close the table.

   close table payroll

Why Close Tables?

Closing tables ensures that:

• All changes to the table are saved.
• System resources are freed up.
• The program avoids potential issues related to open file handles or data locks.

Using close table tablename is the recommended practice to maintain the integrity and efficiency of your Sheerpower applications.

Performance & Concurrency

Fast Table Scanning with Full Concurrency

Sheerpower's integrated database delivers table scanning speeds exceeding 1.5 million records per second with 1000 byte records on modern consumer PCs. This performance is achieved through:

• Optimized index-based extraction
• Efficient in-memory collection processing
• Streamlined two-phase access model (extract then for each)
• Global shared memory cache per table

Full Record-Level Concurrency

Sheerpower provides record-level concurrency, enabling multiple users to safely access and modify data simultaneously without blocking entire tables. This means:

• Multiple users can read and write to the same table concurrently
• Only the specific records being modified are locked, not whole tables or pages
• Other users experience minimal wait times, even under heavy concurrent load
• No complex transaction management is required by the application developer

Performance Example

On a typical modern PC, this operation completes in well under one second, even with concurrent users accessing the same table.

add table todos
  todos(id)            = _gid$   // globally unique, generated locally
  todos(status)        = 'open'
  todos(priority)      = priority$
  todos(description)   = desc$
  todos(last_datetime) = fulltime$
end add

set table customers, field customer_id: key id$