Home • About • Quick Start • Tutorial • Benchmark • Locale • SQL Guide • Changes • Wiki • Türkçe

AmberDB for SQL Developers: A Comparative Practical Guide

This guide is designed for software engineers coming from traditional relational database management systems (RDBMS / SQL) who want to quickly build applications with AmberDB. Rather than focusing on abstract theory or database philosophy, it adopts a direct "In SQL it is done like X, in AmberDB it is done like Y" approach with production-ready Perl code examples.

Essential Practical Notes for Developers (Quick Intro)
Basic CRUD Operations (DML)
Querying, Filtering, and Search (SELECT, WHERE, LIKE)
Sorting (ORDER BY) and Multilingual Collation
- 4.1 Numeric and Text Sorting
- 4.2 Multilingual and Turkish Character Collation
Relationships and JOINs: The Core Architectural Difference
- 5.1 SQL Normalized Multi-Table + JOIN Model
- 5.2 AmberDB Embedded Document + match_block Inverted Index Model
Grouping and Filter Facet Counters (GROUP BY vs. Facet)
Transaction Safety and ACID (COMMIT & ROLLBACK)
Data Definition (DDL: CREATE TABLE vs. AmberDB Schema)
Built-in AmberDB Capabilities Beyond Standard SQL
Quick Reference Cheat Sheet
Terminology Glossary

1. Essential Practical Notes for Developers (Quick Intro)

Before writing queries, keep these 4 operational rules in mind:

No External Database Server or Daemon: There is no mysqld or postgres background daemon to start, configure, or connect to over TCP. AmberDB is an embedded Perl object running directly inside your application process:
```
use AmberDB;
my $adb = AmberDB->new(
    cfg  => { user => 'admin', language => 'en' },
    path => { dbase_dir => './dbstore' }
);
```
Records Are Native Perl Arrays (@record): An SQL table row corresponds to a native Perl array ($id, $field1, $field2, ...).
Index 0 is ALWAYS the Primary Key ID: The first element ($record[0]) is the unique identifier. Pass 0 or undef when inserting; insert_id assigns and returns the auto-incremented ID.
Positional Block Indices Instead of Column Names: Instead of named columns (name, price), AmberDB uses positional block indices (1, 2, 3...). Each block can hold scalars, nested ARRAY references, or HASH references directly.

[!NOTE] For deep architectural mechanics, disk file specs, and benchmarks, refer to About AmberDB, Comprehensive Developer Guide, and Large-Scale Benchmark.

2. Basic CRUD Operations (DML)

2.1 INSERT (Single Record)

SQL:

INSERT INTO products (name, price, brand, category_id)
VALUES ('Sony WH-1000XM5', 149.99, 'Sony', 5);

AmberDB:

# [0] ID (0: auto-assigned), [1] Name, [2] Price, [3] Brand, [4] Category ID
my $id = $adb->insert_id("products", 0, "Sony WH-1000XM5", 149.99, "Sony", 5);

Explanation: insert_id accepts the table identifier, the ID (0 for new records), followed by the field values. It returns the generated numeric ID. All configured inverted indexes are updated synchronously.
Reference: User Guide Section 3.1: insert_id

2.2 BULK INSERT (Batch Ingestion)

SQL:

INSERT INTO products (name, price, brand) VALUES
  ('Item 1', 10.00, 'A'),
  ('Item 2', 20.00, 'B'),
  ('Item 3', 30.00, 'C');

AmberDB:

my @records = (
    [ 0, "Item 1", 10.00, "A" ],
    [ 0, "Item 2", 20.00, "B" ],
    [ 0, "Item 3", 30.00, "C" ],
);
my $status = $adb->insert_list("products", @records);
# $status->{1} = first assigned ID, $status->{total}, etc.

Explanation: insert_list ingests record arrays in a single atomic pass, minimizing file descriptor I/O and lock acquisition costs compared to iterative single inserts. Note that @records is passed as a list, without backslash.
Reference: User Guide Section 8: Batch ETL & Ingestion

2.3 SELECT by ID (Primary Key Point Read)

SQL:
```
SELECT * FROM products WHERE id = 101;
```

AmberDB:

my @product = $adb->read_id("products", 101);
if (@product) {
    print "ID: $product[0], Name: $product[1], Price: $product[2]\n";
}

Explanation: read_id looks up the primary key directly from Berkeley DB with zero SQL parsing or query optimizer overhead ($O(1)$ direct hash seek). Returns an empty list if not found.
Reference: User Guide Section 3.2: read_id

2.4 UPDATE by ID (Single Record Mutation)

SQL:

UPDATE products
SET price = 129.99
WHERE id = 101;

AmberDB:

my @p = $adb->read_id("products", 101);
$p[2] = 129.99; # Mutate Price in Block 2
$adb->update_id("products", @p);

Explanation: AmberDB stores records as cohesive documents. The recommended idiom is reading the record array, modifying the desired indices, and passing @p back to update_id. Since $p[0] holds 101, the table name and array are sufficient.
Reference: User Guide Section 3.3: update_id

2.5 BULK UPDATE (Batch Mutation)

SQL:

UPDATE products SET price = 139.99 WHERE id = 101;
UPDATE products SET price = 549.99 WHERE id = 102;

AmberDB:

my @updates = (
    [ 101, "Sony WH-1000XM5", 139.99, "Sony", 5 ],
    [ 102, "Apple AirPods Max", 549.99, "Apple", 5 ],
);
my $status = $adb->update_list("products", @updates);

Explanation: Pass the array of updated record tuples directly to update_list without backslash. All secondary indexes are synchronized in batch mode.
Reference: User Guide Section 8: update_list

2.6 DELETE (Single Record Deletion & Soft-Delete)

SQL:
```
DELETE FROM products WHERE id = 101;
```
AmberDB:
```
$adb->delete_id("products", 101);
```
Explanation: delete_id removes the record and cleans up all inverted index keys (.inx, .fld, .src, .fac). If the schema defines keep_deleted => 1, the record is automatically moved to a .del archive rather than permanently purged.
Reference: User Guide Section 3.4: delete_id

2.7 BULK DELETE (Batch Deletion)

SQL:

DELETE FROM products WHERE id IN (101, 102, 103);

AmberDB:

my $status = $adb->delete_list("products", 101, 102, 103);

Explanation: Accepts a list of IDs to delete in a single batch pass.
Reference: User Guide Section 8: delete_list

2.8 COUNT(*) (Table Record Count)

SQL:
```
SELECT COUNT(*) FROM products;
```

AmberDB:

my $total = $adb->table_count("products");

Explanation: Reads the active key count directly without scanning table rows.
Reference: User Guide Section 15: Low-Level Table Operations

3. Querying, Filtering, and Search (SELECT, WHERE, LIKE)

3.1 Exact Match (WHERE field = value)

SQL:

SELECT * FROM products WHERE category_id = 5;

AmberDB:

# Block 4: category_id
my ($total, @products) = $adb->field_fetch("products", 4, 5);
print "Found $total products in Category 5.\n";

Explanation: When match_block => [4] is declared, the engine retrieves matching records directly from the unified .fld inverted index file under the key "4:5" in $O(1)$ time.
Reference: User Guide Section 4.2: field_fetch

3.2 Multi-Value IN Lookup (WHERE id IN (...))

SQL:

SELECT * FROM products WHERE id IN (10, 25, 42);

AmberDB:

my @products = $adb->read_list("products", [ 10, 25, 42 ]);

Explanation: read_list fetches records for the given ID list in preserved order.
Reference: User Guide Section 4.1: read_list

3.3 Text Search (WHERE col LIKE '%...%' / FTS)

SQL:

-- Via LIKE:
SELECT * FROM products WHERE name LIKE '%headphones%' OR description LIKE '%headphones%';

-- Or via Full-Text Search:
SELECT * FROM products WHERE MATCH(name, description) AGAINST('headphones');

AmberDB:

my ($total, @results) = $adb->search_table("products", "headphones");
foreach my $item (@results) {
    print "Matched ID: $item->[0], Name: $item->[1]\n";
}

Explanation: search_table searches against .src inverted indexes (or falls back to full scanning in simple mode). It deeply traverses nested lists and hashes, resolving casing and language accents natively via AmberDB::Locale.
Reference: User Guide Section 6: Indexing & Search

3.4 Compound Multi-Field Filtering (WHERE A = x AND B = y)

SQL:

SELECT * FROM products
WHERE category_id = 5 AND brand = 'Sony';

AmberDB:

my $res = $adb->field_filter("products", {
    filter => {
        4 => 5,       # Block 4 (category_id) = 5
        3 => "Sony",  # Block 3 (brand) = "Sony"
    }
});
my @matched_ids = @{ $res->{ids} };
my @records = $adb->read_list("products", \@matched_ids); # or
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

3.5 Pagination (LIMIT & OFFSET)

SQL:

SELECT * FROM products
ORDER BY id DESC
LIMIT 20 OFFSET 40;

AmberDB:

# 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

4. Sorting (ORDER BY) and Multilingual Collation

4.1 Numeric and Text Sorting

SQL:

SELECT * FROM products
WHERE category_id = 5
ORDER BY price ASC;

AmberDB:

# 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

4.2 Multilingual and Turkish Character Collation

SQL:

SELECT * FROM members
ORDER BY name COLLATE utf8mb4_turkish_ci;

AmberDB:

# 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

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:

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):

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):

{
    match_block => [ 3 ], # Index all nested item IDs automatically
}

Query: "Find all orders containing Product 101":

AmberDB Code:

my @orders = $adb->field_fetch("orders", 3, 101);
foreach my $ord (@orders) {
    print "Order ID: $ord->[0], Customer: $ord->[1]\n";
}

Why No JOIN is Needed: All block matching indexes are consolidated into a single <tableid>.fld file (orders.fld). When an order is saved, IDs 101 and 105 inside the nested array are indexed under keys "3:101" and "3:105". Querying field_fetch("orders", 3, 101) executes a single direct hash seek on "3:101" in $O(1)$ time.
Result: Zero JOIN overhead, no secondary order_items junction table, and absolute data encapsulation.
Reference: User Guide Section 9: match_block & Section 24.2: Low-I/O Relationships

In e-commerce, sidebar facet counters such as "Sony (12), Apple (8), Samsung (5)" require SQL GROUP BY aggregates.

SQL:

SELECT brand, COUNT(*) AS count
FROM products
WHERE category_id = 5
GROUP BY brand;

AmberDB (field_fltkeys Facet Engine):

# 1. Fetch Facet Counts (Returns count map only; does not fetch record bodies):
# When facet_block => [ 3 ] is declared in schema:
my $facets = $adb->field_fltkeys("products", {
    target_block => 3,          # Target attribute block to aggregate (Brand)
    filter       => { 4 => 5 }, # Active filter condition: Block 4 (Category ID) = 5
});
# $facets returns: { "Sony" => 12, "Apple" => 8, "Samsung" => 5 }

# 2. Fetch Records When User Selects a Facet (read_list):
# When user clicks "Sony", retrieve matching records:
my $res = $adb->field_filter("products", {
    filter => { 4 => 5, 3 => "Sony" }
});
my @records = $adb->read_list("products", $res->{ids});
# (Or directly for single block: my @prods = $adb->field_fetch("products", 3, "Sony");)

Explanation: field_fltkeys returns the distribution count map directly from .fac columnar indexes in microseconds, avoiding heavy SQL GROUP BY temporary table builds. Records are then retrieved in batch via read_list.
Reference: User Guide Section 16: Facet System

7. Transaction Safety and ACID (COMMIT & ROLLBACK)

AmberDB provides crash-safe undo-log transaction management and Strict 2PL (Two-Phase Locking).

SQL:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK;

AmberDB:

# 1. Begin Transaction
$adb->transact_start();

my @sender   = $adb->read_id("accounts", 1);
my @receiver = $adb->read_id("accounts", 2);

if ($sender[1] >= 100) {
    $sender[1]   -= 100;
    $receiver[1] += 100;
    
    $adb->update_id("accounts", @sender);
    $adb->update_id("accounts", @receiver);
} else {
    # Report error (triggers automatic rollback during transact_end)
    $adb->transact_error("accounts", "Insufficient funds");
}

# 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 journaled to .txn. On failure or explicit rollback, all modified files (.db, .del, .aut, and indexes) are restored in reverse (LIFO) order. transact_recover automatically resolves incomplete transactions after abrupt server power outages.
Reference: User Guide Section 7: Transactions & Recovery

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

SQL: Strict column typing, migration scripts (ALTER TABLE), and separate index definitions:

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:

$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

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 daemons required: in-process L1 object caching (set_cache, get_cache) plus OS-level physical RAM-Disk acceleration (use_ramdisk, ramdisk_*) running directly on Berkeley DB files. Zero network hops, zero service dependencies. | | 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 · Section 13: Shared RAM Cache · 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

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 for critical and ephemeral tables with TTL support. |

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	Go to GitHub issues (only if GitHub is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

AmberDB for SQL Developers: A Comparative Practical Guide

Table of Contents

1. Essential Practical Notes for Developers (Quick Intro)

2. Basic CRUD Operations (DML)

2.1 INSERT (Single Record)

2.2 BULK INSERT (Batch Ingestion)

2.3 SELECT by ID (Primary Key Point Read)

2.4 UPDATE by ID (Single Record Mutation)

2.5 BULK UPDATE (Batch Mutation)

2.6 DELETE (Single Record Deletion & Soft-Delete)

2.7 BULK DELETE (Batch Deletion)

2.8 COUNT(*) (Table Record Count)

3. Querying, Filtering, and Search (SELECT, WHERE, LIKE)

3.1 Exact Match (WHERE field = value)

3.2 Multi-Value IN Lookup (WHERE id IN (...))

3.3 Text Search (WHERE col LIKE '%...%' / FTS)

3.4 Compound Multi-Field Filtering (WHERE A = x AND B = y)

3.5 Pagination (LIMIT & OFFSET)

4. Sorting (ORDER BY) and Multilingual Collation

4.1 Numeric and Text Sorting

4.2 Multilingual and Turkish Character Collation

5. Relationships and JOINs: The Core Architectural Difference

5.1 SQL Normalized Multi-Table + JOIN Model

5.2 AmberDB Embedded Document + match_block Inverted Index Model

Query: "Find all orders containing Product 101":

6. Grouping and Filter Facet Counters (GROUP BY vs. Facet)

7. Transaction Safety and ACID (COMMIT & ROLLBACK)

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

9. Built-in AmberDB Capabilities Beyond Standard SQL

10. Quick Reference Cheat Sheet

11. Terminology Glossary

Keyboard Shortcuts