Inserting Data

Single-Row Insert

A single record is inserted using the insert into statement with named field-value pairs.

Syntax:

insert into <bucket> (<field>: <value>, ...);

Example:

insert into Names (first_name: "Bob", last_name: "Marley");

Fields that do not exist in the bucket schema are silently ignored. Fields that are present in the schema but omitted from the insert will hold a null value for that record.

Positional Insert

When inserting a single record into a bucket whose field order is known, values may be provided positionally without field names.

Syntax:

insert into <bucket> (<value>, <value>, ...);

Example:

insert into Sales ("Laptop", 999.99, 2, 1999.98, 1);

Values are assigned to fields in the order they were declared in the bucket’s schema.

Batch Insert

Multiple records can be inserted in a single statement by providing each record as a bracketed, comma-separated list of field-value pairs, with individual records separated by commas.

Syntax:

insert into <bucket> (
    [<field>: <value>, ...],
    [<field>: <value>, ...],
    ...
);

Example:

insert into TestBatch2 (
    [name: "Bob",     age: 25, city: "LA"],
    [name: "Charlie", age: 35, city: "SF"],
    [name: "Diana",   age: 28, city: "Seattle"]
);

Batch inserts participate in the active transaction in the same manner as single-row inserts. If the transaction is rolled back, none of the rows from the batch are persisted.

Querying Data

The get statement is the primary mechanism for retrieving records from a bucket. It supports field projection, filtering, ordering, and limiting.

Basic Retrieval

Syntax:

get <fields> from <bucket>;
get * from <bucket>;

* is a wildcard that selects all fields. A comma-separated list of field names may be provided to project specific fields.

Example:

get * from Names;
get name, price from Products;

Head and Tail Retrieval

The head and tail functions limit the result set to the first or last n records, respectively.

Syntax:

get head(<n>) from <bucket>;
get tail(<n>) from <bucket>;

Example:

get head(2) from Names;
get tail(2) from Names;

Filtering with WHERE

A where clause constrains the result set to records satisfying the given condition. Conditions can test equality, inequality, comparison, pattern matching, null checks, and logical combinations.

Syntax:

get <fields> from <bucket> where <condition>;

Comparison operators:

Operator	Description
=	Equality.
!=	Inequality.
>	Greater than.
<	Less than.
>=	Greater than or equal to.
<=	Less than or equal to.
like	Pattern matching. Supports % as a multi-character wildcard.
is null	Tests whether a field holds no value.
is not null	Tests whether a field holds a value.

Conditions may be combined with and and or logical operators.

Examples:

get * from Products where in_stock = true;
get * from Products where price > 3 and in_stock = true;
get name from Products where name like "D%" and price > 4 and in_stock = true;
get * from Names where non_existent_field is null;
get * from Names where first_name = "Alice";

A field referenced in a where clause that does not exist in the bucket schema is treated as null for all records. This allows filtering such as where non_existent_field is null to match every record in the bucket.

Ordering Results

The order by clause sorts the result set by a specified field in ascending or descending order.

Syntax:

get <fields> from <bucket> [where <condition>] order by <field> asc|desc;

Examples:

get * from Products order by price asc;
get name, price from Products where in_stock = true order by price desc;

Retrieving the Internal Hash

The internal record identifier can be retrieved by specifying __hash__ in the field list.

Example:

get __hash__ from Names where first_name = "Alice";

Updating Data

The set statement modifies field values on all records that satisfy a where condition. Any number of field assignments may be provided. String functions such as upper() and title() may be used on the right-hand side of an assignment.

Syntax:

set <bucket> ( <field>: <value>, ... ) where <condition>;

Examples:

set TestBatch2 ( name: "Eve", age: 22, city: "Chicago" ) where name = "Alice";
set TestBatch2 ( name: upper("billy") ) where name = "Bob";

All records matching the where clause are updated. If no records match, the statement has no effect. The set statement participates in the active transaction.

Deleting Data

The delete statement removes all records from a bucket that satisfy the given condition.

Syntax:

delete from <bucket> where <condition>;

Examples:

delete from Products where name = "Doohickey";
delete from TestBatch2 where age >= 31;

All matching records are deleted. The delete statement participates in the active transaction.