D.2 Cluster Arrays

Working with Cluster Arrays

A cluster array in SheerPower is a powerful data structure that functions like an in-memory spreadsheet, containing rows and columns of data. They are ideal for handling collections of related information efficiently. SheerPower includes a rich set of features that allows you to work with this data, performing operations such as sorting, filtering, and searching much like you would in a database.

Graphic 1: A Cluster Array as a Spreadsheet

This `inventory` cluster holds multiple records (rows), each with the same set of fields (columns).

item_id$	product_name$	quantity	price
WID-001	Standard Widget	150	19.99
GAD-002	Super Gadget	75	29.95
DOO-003	Doodad Pro	200	9.50

With enough RAM and pagefile space, cluster arrays can support up to one billion rows with key lookup speeds of millions per second.

1. Defining and Populating a Cluster Array

First, you define the structure of your cluster. Then, you can add records (rows) to it using the add cluster statement.

! Define the structure for our inventory cluster inventory: item_id$, product_name$, quantity, price ! Add the first record to the cluster array add cluster inventory inventory->item_id$ = "WID-001" inventory->product_name$ = "Standard Widget" inventory->quantity = 150 inventory->price = 19.99 ! Add a second record add cluster inventory inventory->item_id$ = "GAD-002" inventory->product_name$ = "Super Gadget" inventory->quantity = 75 inventory->price = 29.95

2. Understanding the "Current Row"

SheerPower keeps track of a current row within the cluster array. When you add a new record, that new record automatically becomes the current row. To work with a different record, you must first make it current using the set cluster statement.

Graphic 2: Moving the Current Row Pointer

	item_id$	product_name$
	WID-001	Standard Widget
⇒	GAD-002	Super Gadget
	DOO-003	Doodad Pro

After set cluster inventory: row 2, the second row is now the current one.

! After adding the Super Gadget, the second row is current print "Current product: "; inventory->product_name$ ! Let's make the first row current and print its name set cluster inventory: row 1 print "First product: "; inventory->product_name$

3. Updating and Deleting Records

Modifying a record is simple: first make the record current, then assign a new value to its field.

! Let's update the quantity of the first item set cluster inventory: row 1 inventory->quantity = 145 ! Update the quantity print "Updated quantity: "; inventory->quantity

Deleting records is also straightforward. For a detailed guide on removing rows, see the Reset Cluster and Delete From Cluster tutorial.

4. Iterating Over Records: The `collect` and `for each` Pattern

To process every record in a cluster array, you use a two-step pattern. This design is powerful because it separates the act of gathering/filtering data from the act of processing it.

collect ... end collect: This block builds a temporary collection (or view) of your data. This is where you do all your filtering (include/exclude) and sorting.
for each ... next: This block then loops through the temporary collection you just created.

! First, create a collection of items with low stock (< 100), sorted by name collect cluster inventory include inventory->quantity < 100 sort by inventory->product_name$ end collect ! Now, loop through that filtered collection and print each item for each inventory print inventory->product_name$; " (Quantity: "; inventory->quantity; ")" next inventory

5. Built-in Functions for Instant Data Aggregation

SheerPower provides built-in functions that can instantly calculate aggregate values across an entire cluster array field, eliminating the need for manual loops.

print "Total items in stock: "; sum(inventory->quantity) print "Most expensive item: $"; max(inventory->price) print "Cheapest item: $"; min(inventory->price) print "Number of different products: "; size(inventory)

6. High-Speed Searching with `findrow()`

The findrow() function is highly optimized to perform millions of lookups per second. It returns the row number of the first record that matches your search criteria, or 0 if no match is found.

! Let's find the "Super Gadget" item_to_find$ = "Super Gadget" row_num = findrow(inventory->product_name$, item_to_find$) if row_num > 0 then set cluster inventory: row row_num print "Found "; item_to_find$; " at row "; row_num print "Its price is $"; inventory->price else print item_to_find$; " not found." end if

Note: When you use findrow(), the field you search on is automatically optimized as a key field for future lookups, making subsequent searches even faster. This happens dynamically without any need for manual index configuration. A successful findrow() automatically make the row active.

Summary: Cluster arrays are a cornerstone of efficient data handling in SheerPower. They combine the simplicity of a spreadsheet with the power of high-speed, database-like operations. By mastering their use, you can build applications that are both powerful and easy to maintain.

Hide Description

a$ b$ x y

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

Looking for the full power of Sheerpower?
Check out the Sheerpower website. Free to download. Free to use.