AmberDB

 view release on metacpan or  search on metacpan

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

  # 2. Complete Transaction (Auto-rollback on error, otherwise commit)
  my $txn = $adb->transact_end();
  if ($txn->{status} eq "commit") {
      print "Transfer successful!\n";
  } else {
      print "Transaction aborted and changes rolled back!\n";
  }
  ```

* **Explanation:** Mutations between `transact_start` and `transact_end` are recorded in disk-backed undo journals (`dbstore/journal/txn_*`). On failure or explicit rollback, all modified files (`.db`, `.del`, `.aut`, and indexes) are restored in rev...
* **Reference:** [User Guide Section 7: Transactions & Recovery](EN.AmberDB_User-Guide.html#7-transaction-safety-acid-guarantees-and-recovery-transactions)

---

## 8. Data Definition (DDL: CREATE TABLE vs. AmberDB Schema)

* **SQL:** Strict column typing, migration scripts (`ALTER TABLE`), and separate index definitions:
  ```sql
  CREATE TABLE products (
      id INT PRIMARY KEY AUTO_INCREMENT,
      name VARCHAR(255) NOT NULL,
      price DECIMAL(10,2) NOT NULL,
      brand VARCHAR(100),
      category_id INT,
      INDEX idx_cat (category_id),
      FULLTEXT idx_src (name)
  );
  ```

* **AmberDB:**
  - **Schemaless Operation:** Small applications can immediately write records via `$adb->insert_id("products", ...)` without defining schemas.
  - **Schema Declaration (`products.table` or `table_attr`):** For indexed and large-scale tables, declare index roles in a single Perl Hash:
  ```perl
  $adb->table_attr("products", {
      match_block  => [ 4 ],       # Category ID exact matching (.fld)
      search_block => [ 1 ],       # Title full-text search (.src)
      sort_block   => [ 2 ],       # Price pre-sorting (.inx)
      facet_block  => [ 3, 4 ],    # Brand and Category facet counters (.fac)
      slug_block   => [ 1 ],       # Auto-generate URL slugs from Title (.slg)
      keep_deleted => 1,           # Soft-delete recycle bin archive (.del)
      log_owner    => 1,           # Audit ledger (.aut)
  });
  ```

* **Explanation:** AmberDB schemas do not enforce rigid scalar data types, allowing NoSQL flexibility while automating inverted index derivation.
* **Reference:** [User Guide Section 9: Schema Configuration](EN.AmberDB_User-Guide.html#9-schema-configuration-table-and-in-memory)

---

## 9. Built-in AmberDB Capabilities Beyond Standard SQL

Common tasks that require external libraries, database triggers, or external cache daemons in SQL stacks are natively built into AmberDB:

| Feature | Standard Solution in SQL | Built-in AmberDB Solution |
|---|---|---|
| **Automatic SEO URL Slugs** | Custom slugify code, database uniqueness check queries. | `slug_block => [1]` automatically creates collision-free slugs like `/product/sony-wh-1000xm5` upon insert/update (`get_slug`). |
| **User Audit Trail (Audit Log)** | Separate audit tables, database triggers, or ORM event hooks. | `log_owner => 1` logs every modification into `.aut`. Call `$adb->auth_view("table", $id)` for an instant HTML timeline. |
| **High-Speed In-Memory Caching & Ephemeral Data (RAM-Disk)** | Installing and managing external caching servers (`Redis` or `Memcached`) to bypass disk I/O bottlenecks, managing TCP network overhead and cache-sync logic. | Zero external servers or ...
| **Safe Soft-Delete** | Adding `is_deleted` column and remembering `WHERE is_deleted = 0` on every query. | `keep_deleted => 1` moves deleted records to a `.del` archive. Prevents data leaks with zero query filtering overhead. |

* **Reference:** [User Guide Section 12: URL Slugs](EN.AmberDB_User-Guide.html#12-automated-url-slug-management) · [Section 13: Shared RAM Cache](EN.AmberDB_User-Guide.html#13-unified-shared-ram-cache-db--inx-and-buffer) · [Section 17: Audit Trail]...

---

## 10. Quick Reference Cheat Sheet

A side-by-side mapping for common database operations:

| SQL Statement | AmberDB Method | AmberDB Usage Example |
|---|---|---|
| `INSERT INTO t VALUES (...)` | `insert_id` | `$id = $adb->insert_id("t", 0, @fields);` |
| `INSERT INTO t VALUES (...), (...)` | `insert_list` | `$adb->insert_list("t", @records);` |
| `SELECT * FROM t WHERE id = ?` | `read_id` | `my @rec = $adb->read_id("t", $id);` |
| `SELECT * FROM t WHERE id IN (...)` | `read_list` | `my @recs = $adb->read_list("t", $res->{ids});` |
| `SELECT * FROM t LIMIT 20 OFFSET 0` | `read_all` | `my ($tot, @recs) = $adb->read_all("t", { offset => 0, limit => 20 });` |
| `UPDATE t SET ... WHERE id = ?` | `update_id` | `$adb->update_id("t", @updated_rec);` |
| `DELETE FROM t WHERE id = ?` | `delete_id` | `$adb->delete_id("t", $id);` |
| `DELETE FROM t WHERE id IN (...)` | `delete_list` | `$adb->delete_list("t", @id_list);` |
| `SELECT COUNT(*) FROM t` | `table_count` | `my $count = $adb->table_count("t");` |
| `SELECT * FROM t WHERE col = val` | `field_fetch` | `my @recs = $adb->field_fetch("t", $blk, $val);` |
| `SELECT * FROM t WHERE col LIKE '%s%'` | `search_table` | `my @recs = $adb->search_table("t", "term");` |
| `SELECT * FROM t WHERE a=? AND b=?` | `field_filter` | `$adb->field_filter("t", { filter => { 1 => $a, 2 => $b } });` |
| `SELECT col, COUNT(*) GROUP BY col` | `field_fltkeys`| `$adb->field_fltkeys("t", { target_block => $blk, filter => { $fld => $val } });` |
| `START TRANSACTION` / `COMMIT` | `transact_*` | `$adb->transact_start(); ... $adb->transact_end();` |
| `CREATE TABLE` / `CREATE INDEX` | `table_attr` | `$adb->table_attr("t", { match_block => [ 1, 2 ] });` |

* **For Full Method Inventory:** [User Guide Section 23: Method Quick Reference Table](EN.AmberDB_User-Guide.html#23-method-quick-reference-table)

---

## 11. Terminology Glossary

Mapping SQL and relational concepts to AmberDB architecture:

| SQL / Relational Concept | AmberDB Equivalent | Technical Meaning |
|---|---|---|
| **Database Server / Instance** | `AmberDB` Object (`$adb`) | No background daemon or TCP port; lives embedded inside the application process. |
| **Table** | Table (`.db`) | Key-value store backed by Berkeley DB (`DB_File` Hash). |
| **Row / Record** | Array Record (`@record`) | Flexible Perl list rather than fixed-width C struct. Holds scalars, `ARRAY` refs, or `HASH` refs. |
| **Column / Field** | Block Index (`$record[$i]`) | Positional block index (`1`, `2`, `3`...) instead of column names. |
| **Primary Key (AUTO_INCREMENT)** | Index 0 (`$record[0]`) | The first element of the array. Auto-assigned monotonic numeric ID. |
| **Foreign Key & JOINs** | Nested Arrays & `match_block` | Denormalized documents with embedded lists; inverted index (`.fld`) provides $O(1)$ relationship lookups with zero JOINs. |
| **Index (`CREATE INDEX`)** | Schema Index Blocks | Inverted secondary indexes: Match (`.fld`), Full-text (`.src`), Facet (`.fac`), Sort (`.inx`). |
| **Query Optimizer / Planner** | Direct Key Seeks | Zero SQL parsing and cost compilation; binary RID blocks are read directly from disk. |
| **Collation / Charset** | `AmberDB::Locale` | Embedded multi-language and Turkish alphabet folding without external libc collation dependencies. |
| **Audit Table & Triggers** | `log_owner` & `.aut` | Built-in modification tracking recording user and timestamp. |
| **Soft-Delete (`is_deleted`)** | `keep_deleted` & `.del` | Deleted records are isolated in an archive file, preventing accidental data leaks. |
| **External Cache / Session Server (Redis / Memcached Alternative)** | `set_cache` / `get_cache` & RAM-Disk (`use_ramdisk`) | Without managing a separate daemon/server: in-process L1 object caching and OS-level shared memory RAM-Disk acceleration fo...



( run in 2.510 seconds using v1.01-cache-2.11-cpan-99fa8243a42 )