view release on metacpan or  search on metacpan

docs/EN.AmberDB-vs-SQL_User-Guide.md  view on Meta::CPAN

  my @records = $adb->read_list("products", $res->{ids});
  ```

* **Explanation:** `field_filter` combines index lookups using fast binary set intersections (bitmask AND/OR) and returns matching IDs. `read_list` reads the full records.
* **Reference:** [User Guide Section 4.3: field_filter](EN.AmberDB_User-Guide.html#43-field_filter--multi-criteria-faceted-filtering)

---

### 3.5 Pagination (LIMIT & OFFSET)

* **SQL:**
  ```sql
  SELECT * FROM products
  ORDER BY id DESC
  LIMIT 20 OFFSET 40;
  ```

* **AmberDB:**
  ```perl
  # Parameters: tableid, \%options (offset, limit, sort)
  my ($total, @page) = $adb->read_all("products", { offset => 40, limit => 20, sort => { reverse => 1 } });
  print "Displaying 40-60 of $total records:\n";
  ```

* **Explanation:** `read_all` performs direct zero-copy byte offset slicing on the 8-byte binary ID array in `.inx`, completely avoiding SQLite/MySQL offset scan degradation on deep pages.
* **Reference:** [User Guide Section 4.1: read_all](EN.AmberDB_User-Guide.html#41-sequential-and-bulk-reading)

---

## 4. Sorting (ORDER BY) and Multilingual Collation

### 4.1 Numeric and Text Sorting

* **SQL:**
  ```sql
  SELECT * FROM products
  WHERE category_id = 5
  ORDER BY price ASC;
  ```

* **AmberDB:**
  ```perl
  # Dynamic sorting inside field_fetch:
  # tableid, block_index, block_val, \%options
  my ($total, @sorted) = $adb->field_fetch(
      "products", 4, 5,
      { offset => 0, limit => 20, sort => { blk => 2, reverse => 0 } } # Block 2 (price) ascending
  );
  ```

* **Explanation:** If `sort_block => [2]` is defined in the schema, the engine utilizes pre-sorted binary ID arrays in `.inx` to stream sorted records without runtime in-memory quicksort overhead.
* **Reference:** [User Guide Section 6.4: Sort Index](EN.AmberDB_User-Guide.html#64-sorting-mechanism-and-usage-guide)

---

### 4.2 Multilingual and Turkish Character Collation

* **SQL:**
  ```sql
  SELECT * FROM members
  ORDER BY name COLLATE utf8mb4_turkish_ci;
  ```

* **AmberDB:**
  ```perl
  # Configured language collation applies automatically:
  # cfg => { language => 'tr' }
  my ($total, @members) = $adb->read_all("members", { offset => 0, limit => 50, sort => { blk => 1 } });
  ```

* **Explanation:** AmberDB embeds its own multilingual collation engine (`AmberDB::Locale`). Non-ASCII characters (`Ç, Ğ, I, İ, Ö, Ş, Ü`) are properly alphabetized without external OS libc dependencies.
* **Reference:** [AmberDB::Locale Guide](EN.AmberDB-Locale_User-Guide.html)

---

## 5. Relationships and JOINs: The Core Architectural Difference

### 5.1 SQL Normalized Multi-Table + JOIN Model

SQL requires normalizing orders, line items, and products across separate tables joined via foreign keys:

```sql
SELECT o.id AS order_id, o.customer_name, oi.product_id, oi.quantity, p.name AS product_name
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE oi.product_id = 101;
```

**Cost:** Multiple B-Tree traversals, cross-table random disk seeks, temporary sorting buffers, and query-planner CPU overhead.

---

### 5.2 AmberDB Embedded Document + match_block Inverted Index Model

AmberDB embeds line items directly into the order record as a native Perl array reference (`ARRAY ref`):

```perl
my @order = (
    0,                             # [0] Order ID (auto-generated)
    "Ahmet Yılmaz",                # [1] Customer Name
    "2026-09-06",                  # [2] Date
    [                              # [3] Line Items (Nested ARRAY): [ [ ProductID, Qty, Price ], ... ]
        [ 101, 2, 149.99 ],
        [ 105, 1,  49.90 ],
    ],
    { status => "shipped" }        # [4] Metadata (HASH ref)
);

my $order_id = $adb->insert_id("orders", @order);
```

Schema declaration (`orders.table`):
```perl
{
    match_block => [ 3 ], # Index all nested item IDs automatically
}
```

#### Query: "Find all orders containing Product 101":