Integrated Database Details

Working with Database Tables

Integrated Database Access is one of Sheerpower's defining strengths. It is conceptually powerful, yet intentionally simple and safe to use.

This tutorial walks through the core commands for opening tables, navigating records, extracting collections, and performing high-performance lookups—without exposing developers to SQL, injection risks, or fragile transaction logic.

To access the payroll table in a database, you enter the following:

open table payroll: name '@..\data\payroll'

This opens the payroll table with a reference name of PAYROLL. The table is found in the data folder.

The "Current Record"?

When a table is open in Sheerpower, there can be just one record that is considered the active record. This record is called the current record.

All table field references operate on this current record unless you explicitly move to another one.

print payroll(salary)

In this example, the value of the salary field is taken from the current record in the payroll table.

Changing the Current Record

Statements such as EXTRACT TABLE, FOR EACH, and SET TABLE, change which record is considered current. After such a statement executes, all table field references automatically apply to the newly selected record.

If the operation fails to establish a current record, the special variable _extracted is set to zero, otherwise it is the number of records that the operation produced.

ssn$ = '111222222' set table payroll, field ssn: key ssn$ if _extracted = 0 then print 'SSN not found: '; ssn$ stop end if print payroll(salary)

Accessing a Field in the Current Table Record

To read a field value from the current record of a table, specify the table name followed by the field name in parentheses.

print payroll(salary)

Updating Field contents in the Current Table Record

To modify a field value, assign a new value to the table field expression.

abc = 100 payroll(salary) = abc

Accessing a Table Field Using an Expression

Sheerpower allows table fields to be referenced dynamically using expressions. This can be done by field name or by field position.

// Access by field name myfield$ = 'salary' print payroll(#myfield$) // Access by field number field_nbr = 3 print payroll(#field_nbr)

The EXTRACT / END EXTRACT Block

The EXTRACT / END EXTRACT block scans a table row by row. As each row is processed, it becomes the current record.

While scanning the table, EXTRACT builds a COLLECTION of records. This collection may contain all records in the table, or a filtered subset created using INCLUDE and EXCLUDE statements. The collection may also use one or more sort by, sort descending by, or sort nocase by clauses.

Note on sorting: Sheerpower integrates sorting into normal procedural control flow, allowing the sort key for each record to be selected by ordinary executable statements. Sort keys can also be expressions, providing additional flexibility when determining sort order. Sorting is stable: rows with equal keys always keep their relative order.

Iterating Through the Collection

Once the EXTRACT phase is complete, the resulting collection is iterated using a FOR EACH / NEXT loop.

If sorting was requested, the sort is performed only when needed and delayed until the FOR EACH statement begins execution.

During the loop, each record in the collection becomes the current record in turn.

for each payroll print payroll(salary) next

extract table products include products(in_stock) > 0 sort by products(product_code) end extract print 'Products found: '; _extracted for each products print products(product_code); " - "; products(name) next products

Record Locking and Concurrency

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

When a table is opened for update or output, records are automatically 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.

Automatic 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 delayed writes, allowing updates to be batched efficiently.

Explicit Unlocking and Commit Control

To explicitly unlock a table, use:

increase = 100 lock table payroll payroll(salary) = payroll(salary) + increase unlock table payroll

To unlock all tables, use:

unlock all

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

unlock table payroll: commit

To commit all writes for all tables, use:

unlock all: commit

For efficiency, this is typically done at the top of transaction loops, at transaction boundaries, or after a batch of transactions has been processed.

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.

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
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
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
Key Range Lookup:
- Uses the extract table statement with the key ... to ... clause to specify a range of key values for retrieval.
- Syntax:
  extract table table_name, field key_field: key 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

Note: Unlike SQL and many other query languages, Sheerpower is careful to separate data from functions. This makes it impossible for hackers to launch SQL INJECTION ATTACKS against a website that uses Sheerpower on the back end.

In order to access a specific record in a table, it must be current. A successful key lookup causes a record to be current. Within an extract/end extract block, each record becomes current. Within a for each/next block, each record also becomes current.

In general, table records are first collected using extract/end extract and then iterated through using for each/next. This is done as two steps in order for collected records to be optionally sorted.

After any operation that could make a record current, the special variable _extracted will be zero if no record was found or will reflect the number of records found. Inside the for each block the special variable _pointer is the current iteration counter.
An extract/end extract block can contain:

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:

extract table employees exclude employees(age) < 18 exclude employees(status) = 'inactive' end extract for each employees print employees(name); " - "; employees(age) next employees

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:

extract table sales include sales(region) = 'North' include sales(amount) > 1000 end extract for each sales print sales(id); " - "; sales(region); " - $"; sales(amount) next sales

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:

extract table orders if orders(date) < '2023-01-01' then exit extract end if end extract for each orders print orders(id); " - "; orders(date) next orders

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:

extract table inventory if inventory(quantity) = 0 then cancel extract end if end extract for each inventory print inventory(item); " - "; inventory(quantity) next inventory

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:

extract table customers sort by customers(last_name) end extract for each customers print customers(last_name); ", "; customers(first_name) next customers

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:

extract table products sort descending by products(price) end extract for each products print products(name); " - $"; products(price) next products

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

sort by an expression

The sort key can be any expression, evaluated once per record — useful when the interesting order lives in a computed value no single field holds. Here order lines are ranked by their extended value, price times quantity:

extract table detail sort descending by detail(price) * detail(quantity) end extract for each detail print detail(prodnbr); ' '; & sprintf$('%.2m', detail(price) * detail(quantity)) next detail

The most valuable lines come first — which need not be the highest-priced products or the biggest quantities, only the largest result of multiplying the two. The expression decides the order and nothing more; no field in the table changes.

sort nocase by xxx

The sort nocase by xxx statement sorts the records in ascending order based on the expression xxx, ignoring the difference between uppercase and lowercase letters. Without nocase, all uppercase letters sort ahead of all lowercase letters ('Zebra' would come before 'apple').

The keyword order is fixed: sort [descending] [nocase] by xxx — nocase goes right before by.

Example:

extract table customers sort nocase by customers(city) end extract for each customers print customers(city); " - "; customers(last_name) next customers

This example sorts the customer records by city so that 'boston', 'Boston', and 'BOSTON' group together. Records whose keys are equal when case is ignored keep their original order.

Case sensitivity is chosen per sort key, so a case-blind major sort can be combined with a case-sensitive minor sort in the same extract:

extract table customers sort nocase by customers(city) ! case-blind major sort sort by customers(last_name) ! case-sensitive minor sort end extract

Conditional sorting — a different sort key per record

The sort by statement is executed once for each record, so it can live inside an if and supply a different expression for different records. A classic use is a fallback key: sort clients by their home zipcode, but for clients who have no home zipcode on file, use their billing zipcode instead:

extract table clients if clients(home_zip) = '' then sort by clients(bill_zip) ! no home zipcode -- fall back else sort by clients(home_zip) end if end extract for each clients print clients(last_name); ' '; clients(home_zip); ' '; clients(bill_zip) next clients

Every record contributes exactly one key value — whichever zipcode its branch chose — and the collection is sorted as a single unified zipcode order.

The one rule: every branch must agree on each sort key's data type, direction, and case sensitivity, and supply the same number of sort keys. Sorting by a string in one branch and a number in another raises a catchable INCONSORT exception (−4112); changing the number of sort keys between records raises BADSORT (−4111).

Statement order inside extract

Statement order inside extract is program order: the statements run top to bottom, once for each record. When an include fails (or an exclude matches), execution of the block stops for that record — statements below it, including sort by, never run for the rejected record, and it contributes no sort key — and therefore never counts toward groupmin or groupmax. So placing include/exclude before sort by skips the key-building work for records that will be discarded. Filters may appear anywhere in the block — before, between, or after the sort keys — and the result is the same; filtering first simply does less work.

groupmin n — finding duplicates

The groupmin n attribute is applied after the sort completes: it keeps only groups — records that are equal on the complete set of sort keys — containing at least n records. Its most common use is the classic data-cleanup question: which values appear more than once?

Here the client table is checked for duplicate phone numbers. Sorting by phone brings equal numbers together; groupmin 2 then keeps every record of every duplicated number, with the one-of-a-kind numbers gone:

extract table clients: groupmin 2 sort by clients(phone) end extract print 'records sharing a phone number: '; _extracted for each clients print clients(phone); ' '; clients(last_name); ', '; clients(first_name) next clients

Each duplicated number arrives with all of its records, grouped together in sorted order — exactly what is needed to review or merge the duplicates. The table itself is untouched.

groupmax n asks the opposite question of the same sort: keep only the groups with at most n records. groupmax 1 on the same table returns the phone numbers that occur exactly once — the complement of the duplicate report:

extract table clients: groupmax 1 sort by clients(phone) end extract print 'records with a one-of-a-kind phone number: '; _extracted for each clients print clients(phone); ' '; clients(last_name); ', '; clients(first_name) next clients

The two compose: extract table clients: groupmin 2, groupmax 2 keeps exactly the pairs. A natural real-world use of groupmax 1 is reconciliation: extract two sources into one collection (or load them into one cluster) with a field marking each record's source, sort by the matching key, and groupmax 1 hands you every key that appears in only one source — the orphans — while groupmin 2 on the same sort gives the matched keys. One sort, both reports.

The unifying idea: a sort key defines groups, and groupmin / groupmax let you select groups by how many records they have without counting anything yourself. groupmin finds the crowded groups, groupmax the sparse ones, and together they find the exactly-right-sized ones — all in the single pass the sort was already doing.

Points worth knowing:

groupmin is written after the colon and comma-chains with the other extract options (extract table clients: cachesize 100, groupmin 2).
A group is defined by the complete set of sort keys, so sorting by two keys finds records duplicated on the pair — e.g. sort by clients(last_name) plus sort by clients(first_name) finds full-name duplicates. Under sort nocase by, values differing only in case count as one group.
When groupmin or groupmax is used, the sort runs immediately at end extract (instead of being delayed), so _extracted is the filtered record count right away.
groupmin 1 keeps everything; a value below 1 raises a catchable GROUPBAD exception (−4114), and groupmin without any sort by is a compile error (GROUPNOSORT, −4113) — groups are defined by the sort keys.
groupmax n is the mirror image: it keeps only groups with at most n records. groupmax 1 finds the one-of-a-kind values, and the two compose — extract table clients: groupmin 2, groupmax 2 keeps exactly the pairs. Same rules (sort required, values below 1 raise GROUPBAD), plus a groupmax below the groupmin raises GROUPBAD too, since no group could ever survive it.

Fuzzy selection — scoring records

Because include and sort by are ordinary executable statements, they can work with values your own code computes as each record goes by. That turns an extract block into a scoring model: award points for each desirable trait, keep the records that score high enough, and rank the survivors best-first. Here house listings are scored on bedrooms and lot position:

budget = 350_000 extract table listings score = 0 if listings(bedrooms) >= 2 then score = score + 50 if listings(cornerlot) = 'y' then score = score + 20 if listings(garage) >= 2 then score = score + 15 if listings(price) <= budget then score = score + 40 if listings(sqft) >= 1_800 then score = score + 10 include score >= 50 sort descending by score end extract for each listings print listings(address) next listings

Each record is scored fresh (score = 0 runs once per record), include keeps only records reaching the threshold, and sort descending by score presents the best matches first. A listing that sweeps every trait scores 135 and leads the list — but a one-bedroom with a two-car garage, priced within budget, and 1,800+ square feet qualifies at 65 points on those traits alone. That is exactly the near-miss a rigid “must have 2+ bedrooms AND ...” filter would discard, and it is what makes the selection fuzzy: no single condition is required, the traits just have to add up.

Notice that the model reads budget — an ordinary variable set before the extract — so the same scoring code serves any buyer. The weights are ordinary numbers your code controls: refining the model is editing a few if lines, not restructuring a query.

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 delayed until the FOR EACH block is executed. This enables dramatic performance improvements if later using the reextract or append options.

open table client : name '@..\safe\client' open table detail: name '@..\safe\detail' extract table client end extract print 'Records found: '; _extracted print if len(a$) > 0 then my_id$ = a$ // try entering 80561 for a$ else my_id$ = '80522' end if print 'Look up this ID: '; my_id$ set table client, field id: key my_id$ if _extracted = 0 then print 'Unknown ID: '; my_id$ else print client(last); ' '; client(first) end if print print 'Clients in California, but excluding those from area code 619' extract table client include client(state) = 'CA' exclude client(phone)[1:3] = '619' sort ascending by client(last) end extract for each client print client(id);' '; client(first); ' '; client(last); ' '; client(phone) next client print print 'Clients excluding those from area code 619' extract table client exclude client(phone)[1:3] = '619' end extract for each client print client(first); ' '; client(last);' ';client(phone) next client print print 'Major and minor sorting' extract table client sort ascending by client(state) sort ascending by client(last) end extract for each client print client(last); ', '; client(first); ' '; client(state) next client print print 'List of clients with last name starting with an R' extract table client, field last: partial key 'R' end extract print for each client print client(first); ' '; client(last) next client print print 'Re-extract from an already extracted table. Perhaps to do some sorting, etc.' extract table client include client(state) = 'CA' end extract reextract table client exclude client(phone)[1:3] = '619' sort ascending by client(last) end extract print 'List of California Clients in Area Code 619' for each client print client(first); ' '; client(last); ' ';client(phone) next client print set table detail: extracted 0 extract table detail, field lineid : & key '10301001' to '10302000', append sort by detail(prodnbr) sort by detail(invnbr) end extract extract table detail, field lineid : & key '10311001' to '10312000', append sort by detail(prodnbr) sort by detail(invnbr) end extract print 'Prod'; tab(7); 'Line ID'; tab(17); 'Quantity' for each detail print detail(prodnbr); tab(7); detail(lineid); & tab(17); detail(qty) next detail

Code Explanation:

1. Open Tables:

These lines open two tables named client and detail from the specified file paths (@..\safe\client and @..\safe\detail). The tables are made available for further operations like extracting records, querying, and sorting.

2. Extract All Records:

This block extracts all records from the client table. The _extracted variable holds the number of records extracted, which is then printed to inform the user how many records were found.

3. Set ID Based on Input:

This conditional block checks if the string a$ has a length greater than 0 (i.e., it is not empty). If a$ is not empty, my_id$ is set to the value of a$; otherwise, my_id$ is set to the default ID '80522'.

4. Lookup Record by ID:

This block sets the client table to look for a record with an id matching my_id$. The _extracted variable will hold the number of matching records (either 0 or 1).

5. Print Client Information:

If no record was found (_extracted = 0), the program prints "Unknown ID". Otherwise, it prints the client's last and first names.

6. Extract Clients from California (Excluding Area Code 619):

This block extracts records from the client table where the state is 'CA', excluding those whose phone number starts with '619'. The results are sorted in ascending order by the last name.

7. Print Extracted Clients:

This loop iterates over each extracted client and prints their ID, first name, last name, and phone number.

8. Extract Clients Excluding Area Code 619:

This block extracts records from the client table, excluding those whose phone number starts with '619'. No sorting is specified.

9. Print Extracted Clients:

This loop iterates over each extracted client and prints their first name, last name, and phone number.

10. Sort Clients by State and Last Name:

This block extracts and sorts clients first by state (major sort) and then by last name (minor sort).

11. Print Sorted Clients:

This loop iterates over each sorted client and prints their last name, first name, and state.

12. Extract Clients with Last Name Starting with 'R':

This block extracts records from the client table where the last name starts with the letter 'R'.

13. Print Clients with Last Name Starting with 'R':

This loop iterates over each client whose last name starts with 'R' and prints their first and last names.

14. Re-extract Clients from California:

This block extracts records from the client table where the state is 'CA'. The records are held for further re-extraction.

15. Re-extract and Sort Clients in Area Code 619:

This block further filters the already extracted records, keeping only those whose phone number starts with '619', and sorts them by last name.

16. Print Sorted California Clients in Area Code 619:

This loop iterates over the re-extracted records and prints the first name, last name, and phone number of clients in California with the '619' area code.

17. Extract Details with Specific Line IDs (First Range):

This block clears previous extractions (extracted 0) and extracts records from the detail table with lineid between '10301001' and '10302000'. The results are sorted by prodnbr and invnbr.

18. Extract Details with Specific Line IDs (Second Range):

This block appends to the previous extraction, extracting records with lineid between '10311001' and '10312000'. The results are sorted similarly by prodnbr and invnbr.

19. Print Header for Details:

Prints a header for the detail records, aligning columns for Prod, Line ID, and Quantity using tab() for spacing.

20. Print Extracted Details:

This loop iterates over each extracted detail record and prints the product number, line ID, and quantity, aligned under the header.

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:

close table tablename

:
Also supported, to close all tables at once is:

close table: all

Example Usage

Opening a Table:
First, you open the table using the open table command.

open table payroll: name '@..\data\payroll'
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
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

// Extract and process 1 million records in under 1 second extract table transactions include transactions(year) = 2024 end extract total = 0 for each transactions total = total + transactions(amount) next transactions print "Processed "; _extracted; " records - Total: $"; total

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

Performance Note:
Problem: Traditional databases often require page or table-level locks, slowing down multi-user access and making them wait on each other.

Solution: Sheerpower uses fine-grained record-level locks with optimized in-memory extraction.

Efficiency: Developers routinely achieve over 1.5M record scans per second, while multiple users access the same tables without blocking each other.

Takeaway: You get both speed and concurrency out of the box, with no need for custom transaction logic.

(Show/Hide ARS Database Engine Details)

How the ARS Database Engine Stores Data

Understanding the physical layout of the ARS database engine helps explain why Sheerpower's database behaves the way it does—why sequential scans are so fast, why multiple keys have no performance hierarchy, and why there is no concept of a primary key.

Data Buckets and Key Buckets

ARS stores records in a linked list of large data buckets. Each bucket holds many records packed together, and the buckets are chained in sequence. This is the physical home of all record data—field values live here, and only here.

Keys are stored separately, in key buckets. Each key bucket holds key values together with pointers back into the data buckets. When you look up a record by key, ARS navigates the key bucket to find the pointer, then follows that pointer directly to the data record.

This separation—data in one structure, keys in another—is the foundation of every performance and concurrency property the engine provides.

Why this matters: In many traditional database engines, records are physically organized around a single "clustering key." Changing that key means moving the record. In ARS, the data record never moves—only the key bucket entry changes. This makes all key updates equally inexpensive, regardless of which key is being changed.

Solution: By decoupling key storage from data storage, ARS treats every key as an equal citizen. There is no primary key that owns the physical layout, and no secondary keys that pay a performance penalty for that reason.

Efficiency: Adding a new key to a table creates a new key record pointing into an existing data bucket. The data records themselves are untouched. Key lookups are fast regardless of how many keys the table carries.

Takeaway: All keys in ARS are changeable, all are equal, and none owns the data. The uniqueness of a record identifier is the application's responsibility—not a database constraint.

No Primary Key—By Design

ARS has no concept of a primary key. Every key defined on a table is simply a key—a navigational path into the data buckets. Any key value can be changed at any time without reorganizing the underlying data.

When a Sheerpower application needs a stable, unique record identifier, use _gid$—a 30-byte globally unique identifier (YYYYMMDD + UUID) generated by the Sheerpower runtime.

Because _gid$ includes a universally unique identifier (UUID), two instances of the same handler running simultaneously will never produce the same value, even when running on different systems. This eliminates the need for counters, sequence generators, or inter-process coordination.

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

Sequential Scanning

Because all data records live in a linked list of large, packed data buckets, a full table scan is a straight sequential walk through those buckets—no index navigation, no random access, no page splits to work around. Modern CPUs and memory controllers are highly optimized for exactly this access pattern. This is why Sheerpower delivers over 1.5 million record scans per second on consumer hardware.

When you write an extract table block without a key clause, ARS walks the data bucket chain from start to finish. The performance you see is a direct consequence of the physical layout—not a tuning achievement.

How the Physical Layout Supports Concurrency

Record-level locking in ARS is straightforward because of the physical separation between key buckets and data buckets. When a record is locked, the lock is on the data record—not on any key entry, not on a page, not on the table. Other processes can still navigate key buckets freely, and can read or write any other data record in the same table without interference.

Changing a key value on a locked record updates only the key bucket entry. The data record itself stays in place in its data bucket. This means a key change and a field value change both touch only the minimum necessary structures—and lock only what they actually modify.

Why this matters: Traditional page-level or table-level locking blocks all readers and writers on that page or table while any single operation is in progress. Under load, this becomes a bottleneck that scales poorly.

Solution: ARS locks individual data records. The lock scope is exactly as wide as the operation requires—one record—and no wider. Readers and writers on other records in the same table proceed without waiting.

Efficiency: The unlock all: commit pattern at the top of a transaction loop releases all locks from the previous request and forces dirty records to be written to storage.

As a result, no locks are held during idle time. Waiting is bounded by the duration of actual computation—not by network or client delays.

Takeaway: Fine-grained record-level locking combined with disciplined commit boundaries means multiple handler instances can read and write the same table simultaneously with little waiting.

Deadlock Diagnostics

Because the ARS database engine is integrated directly into the Sheerpower runtime, deadlock detection has access to information that an external database engine cannot see—the exact source lines executing in each process at the moment the deadlock occurs.

When a deadlock is detected, the Sheerpower diagnostic utility arslockmon reports not just the process IDs involved, but the precise source files and line numbers where each process is waiting.

An investigation that might take hours of log correlation in a traditional setup becomes immediate.

Why SQL Injection Is Structurally Impossible

Many database systems accept queries as strings of text that are parsed and executed at runtime. A malicious user who can control part of that string can inject additional commands—altering the query's intent, bypassing access controls, or destroying data.

Sheerpower's database access works differently at a structural level. There is no query language. There is no string that gets parsed into a command. Key values, field values, and filter expressions are passed directly to the ARS engine as typed data—they are never interpreted as code.

set table customers, field customer_id: key id$

Whatever is in id$ is treated as data. It cannot alter execution. There is no parser to subvert.

Why this matters: SQL injection is one of the most persistent vulnerabilities in web applications.

Solution: ARS separates data from operations at the architectural level.

Efficiency: No sanitization, no parameterization, no developer burden.

Takeaway: The vulnerability class does not exist in this architecture.

Why the Performance Numbers Are Predictable

The 1.5 million records per second figure is a direct result of the physical storage layout.

Sequential scans are linear, cache-friendly, and efficient. Key lookups are shallow and consistent. Shared memory caching reduces disk access. Writes are batched at commit boundaries.

Why this matters: Traditional database performance requires tuning and monitoring.

Solution: ARS performance follows directly from its design.

Efficiency: Multiple handler instances scale naturally.

Takeaway: Development performance matches production.

(Show/Hide Sheerpower Integrated Database Takeaways)

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

Automatic Writes and Unlocking

Explicit Unlocking and Commit Control

Key Lookup Methods in Sheerpower

Sheerpower Extract Block with Examples

exclude logical expression

include logical expression

exit extract

cancel extract

sort by xxx

sort descending by xxx

sort by an expression

sort nocase by xxx

Conditional sorting — a different sort key per record

Statement order inside extract

groupmin n — finding duplicates

Fuzzy selection — scoring records

Explanation

Closing Tables in Sheerpower

Example Usage

Why Close Tables?

Performance & Concurrency

Fast Table Scanning with Full Concurrency

Full Record-Level Concurrency

Performance Example

How the ARS Database Engine Stores Data

Data Buckets and Key Buckets

No Primary Key—By Design

Sequential Scanning

How the Physical Layout Supports Concurrency

Deadlock Diagnostics

Why SQL Injection Is Structurally Impossible

Why the Performance Numbers Are Predictable

Sheerpower Integrated Database Takeaways

Enter or modify the code below, and then click on RUN